<!DOCTYPE html>
<html>
<head>
    

    

    



    <meta charset="utf-8">
    
    
    
    <title>mysql基础使用 | Cucy的博客</title>
    <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">
    
    <meta name="theme-color" content="#3F51B5">
    
    
    <meta name="keywords" content="mysql">
    <meta name="description" content="1. 安装2. 基本使用">
<meta name="keywords" content="mysql">
<meta property="og:type" content="article">
<meta property="og:title" content="mysql基础使用">
<meta property="og:url" content="http://zhourudong.cn/2016/09/10/41-mysql基础使用/index.html">
<meta property="og:site_name" content="Cucy的博客">
<meta property="og:description" content="1. 安装2. 基本使用">
<meta property="og:locale" content="zh-CN">
<meta property="og:image" content="https://raw.githubusercontent.com/cucyzrd/file/master/num.png">
<meta property="og:image" content="https://raw.githubusercontent.com/cucyzrd/file/master/float.png">
<meta property="og:image" content="https://raw.githubusercontent.com/cucyzrd/file/master/char.png">
<meta property="og:image" content="https://raw.githubusercontent.com/cucyzrd/file/master/date_and_time.png">
<meta property="og:updated_time" content="2017-09-08T07:02:37.066Z">
<meta name="twitter:card" content="summary">
<meta name="twitter:title" content="mysql基础使用">
<meta name="twitter:description" content="1. 安装2. 基本使用">
<meta name="twitter:image" content="https://raw.githubusercontent.com/cucyzrd/file/master/num.png">
    
        <link rel="alternate" type="application/atom+xml" title="Cucy的博客" href="/atom.xml">
    
    <link rel="shortcut icon" href="/favicon.ico">
    <link rel="stylesheet" href="/css/style.css?v=1.6.13">
    <script>window.lazyScripts=[]</script>

    <!-- custom head -->
    

</head>

<body>
    <div id="loading" class="active"></div>

    <aside id="menu" class="hide" >
  <div class="inner flex-row-vertical">
    <a href="javascript:;" class="header-icon waves-effect waves-circle waves-light" id="menu-off">
        <i class="icon icon-lg icon-close"></i>
    </a>
    <div class="brand-wrap" style="background-image:url(/img/brand.jpg)">
      <div class="brand">
        <a href="/" class="avatar waves-effect waves-circle waves-light">
          <img src="/img/avatar.png">
        </a>
        <hgroup class="introduce">
          <h5 class="nickname">cucy</h5>
          <a href="mailto:292016176@qq.com" title="292016176@qq.com" class="mail">292016176@qq.com</a>
        </hgroup>
      </div>
    </div>
    <div class="scroll-wrap flex-col">
      <ul class="nav">
        
            <li class="waves-block waves-effect">
              <a href="/"  >
                <i class="icon icon-lg icon-home"></i>
                主页
              </a>
            </li>
        
            <li class="waves-block waves-effect">
              <a href="/archives"  >
                <i class="icon icon-lg icon-archives"></i>
                Archives
              </a>
            </li>
        
            <li class="waves-block waves-effect">
              <a href="/tags"  >
                <i class="icon icon-lg icon-tags"></i>
                Tags
              </a>
            </li>
        
            <li class="waves-block waves-effect">
              <a href="https://github.com/cucy" target="_blank" >
                <i class="icon icon-lg icon-github"></i>
                Github
              </a>
            </li>
        
      </ul>
    </div>
  </div>
</aside>

    <main id="main">
        <header class="top-header" id="header">
    <div class="flex-row">
        <a href="javascript:;" class="header-icon waves-effect waves-circle waves-light on" id="menu-toggle">
          <i class="icon icon-lg icon-navicon"></i>
        </a>
        <div class="flex-col header-title ellipsis">mysql基础使用</div>
        
        <div class="search-wrap" id="search-wrap">
            <a href="javascript:;" class="header-icon waves-effect waves-circle waves-light" id="back">
                <i class="icon icon-lg icon-chevron-left"></i>
            </a>
            <input type="text" id="key" class="search-input" autocomplete="off" placeholder="输入感兴趣的关键字">
            <a href="javascript:;" class="header-icon waves-effect waves-circle waves-light" id="search">
                <i class="icon icon-lg icon-search"></i>
            </a>
        </div>
        
        
        <a href="javascript:;" class="header-icon waves-effect waves-circle waves-light" id="menuShare">
            <i class="icon icon-lg icon-share-alt"></i>
        </a>
        
    </div>
</header>
<header class="content-header post-header">

    <div class="container fade-scale">
        <h1 class="title">mysql基础使用</h1>
        <h5 class="subtitle">
            
                <time datetime="2016-09-10T08:25:08.000Z" itemprop="datePublished" class="page-time">
  2016-09-10
</time>


            
        </h5>
    </div>

    


</header>


<div class="container body-wrap">
    
    <aside class="post-widget">
        <nav class="post-toc-wrap" id="post-toc">
            <h4>TOC</h4>
            <ol class="post-toc"><li class="post-toc-item post-toc-level-1"><a class="post-toc-link" href="#1-安装"><span class="post-toc-number">1.</span> <span class="post-toc-text">1. 安装</span></a></li><li class="post-toc-item post-toc-level-1"><a class="post-toc-link" href="#2-基本使用"><span class="post-toc-number">2.</span> <span class="post-toc-text">2. 基本使用</span></a><ol class="post-toc-child"><li class="post-toc-item post-toc-level-3"><a class="post-toc-link" href="#提示符"><span class="post-toc-number">2.0.1.</span> <span class="post-toc-text">提示符</span></a></li><li class="post-toc-item post-toc-level-3"><a class="post-toc-link" href="#一些基本函数"><span class="post-toc-number">2.0.2.</span> <span class="post-toc-text">一些基本函数</span></a></li><li class="post-toc-item post-toc-level-3"><a class="post-toc-link" href="#注释"><span class="post-toc-number">2.0.3.</span> <span class="post-toc-text">注释</span></a></li></ol></li><li class="post-toc-item post-toc-level-2"><a class="post-toc-link" href="#3-数据库相关操作-DDL数据库定义语言"><span class="post-toc-number">2.1.</span> <span class="post-toc-text">3.数据库相关操作(DDL数据库定义语言)</span></a><ol class="post-toc-child"><li class="post-toc-item post-toc-level-3"><a class="post-toc-link" href="#1-数据库创建"><span class="post-toc-number">2.1.1.</span> <span class="post-toc-text">1.数据库创建</span></a></li><li class="post-toc-item post-toc-level-3"><a class="post-toc-link" href="#2-查看数据库"><span class="post-toc-number">2.1.2.</span> <span class="post-toc-text">2.查看数据库</span></a></li><li class="post-toc-item post-toc-level-3"><a class="post-toc-link" href="#3-查看指定数据库的定义"><span class="post-toc-number">2.1.3.</span> <span class="post-toc-text">3. 查看指定数据库的定义</span></a></li><li class="post-toc-item post-toc-level-3"><a class="post-toc-link" href="#4-修改数据库的编码方式"><span class="post-toc-number">2.1.4.</span> <span class="post-toc-text">4.修改数据库的编码方式</span></a></li><li class="post-toc-item post-toc-level-3"><a class="post-toc-link" href="#5-切换数据库"><span class="post-toc-number">2.1.5.</span> <span class="post-toc-text">5.切换数据库</span></a></li><li class="post-toc-item post-toc-level-3"><a class="post-toc-link" href="#6-删除数据库"><span class="post-toc-number">2.1.6.</span> <span class="post-toc-text">6.删除数据库</span></a></li></ol></li><li class="post-toc-item post-toc-level-2"><a class="post-toc-link" href="#3-mysql-数据类型"><span class="post-toc-number">2.2.</span> <span class="post-toc-text">3. mysql 数据类型</span></a><ol class="post-toc-child"><li class="post-toc-item post-toc-level-3"><a class="post-toc-link" href="#3-1-整数型"><span class="post-toc-number">2.2.1.</span> <span class="post-toc-text">3.1 整数型</span></a><ol class="post-toc-child"><li class="post-toc-item post-toc-level-4"><a class="post-toc-link" href="#3-1-1-TINYINT"><span class="post-toc-number">2.2.1.1.</span> <span class="post-toc-text">3.1.1 TINYINT</span></a></li><li class="post-toc-item post-toc-level-4"><a class="post-toc-link" href="#3-1-2-SMALLINT"><span class="post-toc-number">2.2.1.2.</span> <span class="post-toc-text">3.1.2 SMALLINT</span></a></li><li class="post-toc-item post-toc-level-4"><a class="post-toc-link" href="#3-1-3-MEDIUMINT"><span class="post-toc-number">2.2.1.3.</span> <span class="post-toc-text">3.1.3 MEDIUMINT</span></a></li><li class="post-toc-item post-toc-level-4"><a class="post-toc-link" href="#3-1-4-INT"><span class="post-toc-number">2.2.1.4.</span> <span class="post-toc-text">3.1.4 INT</span></a></li><li class="post-toc-item post-toc-level-4"><a class="post-toc-link" href="#3-1-5-BIGINT"><span class="post-toc-number">2.2.1.5.</span> <span class="post-toc-text">3.1.5 BIGINT</span></a></li><li class="post-toc-item post-toc-level-4"><a class="post-toc-link" href="#3-1-6-BOOLEAN-BOOL"><span class="post-toc-number">2.2.1.6.</span> <span class="post-toc-text">3.1.6 BOOLEAN BOOL</span></a></li><li class="post-toc-item post-toc-level-4"><a class="post-toc-link" href="#测试整型"><span class="post-toc-number">2.2.1.7.</span> <span class="post-toc-text">测试整型</span></a></li><li class="post-toc-item post-toc-level-4"><a class="post-toc-link" href="#无符号整型-UNSIGNED"><span class="post-toc-number">2.2.1.8.</span> <span class="post-toc-text">无符号整型 UNSIGNED</span></a></li><li class="post-toc-item post-toc-level-4"><a class="post-toc-link" href="#零填充ZEROFILL"><span class="post-toc-number">2.2.1.9.</span> <span class="post-toc-text">零填充ZEROFILL</span></a></li></ol></li><li class="post-toc-item post-toc-level-3"><a class="post-toc-link" href="#3-2-浮点型"><span class="post-toc-number">2.2.2.</span> <span class="post-toc-text">3.2 浮点型</span></a><ol class="post-toc-child"><li class="post-toc-item post-toc-level-4"><a class="post-toc-link" href="#3-2-1-FLOAT"><span class="post-toc-number">2.2.2.1.</span> <span class="post-toc-text">3.2.1 FLOAT</span></a></li><li class="post-toc-item post-toc-level-4"><a class="post-toc-link" href="#3-2-2-DOUBLE"><span class="post-toc-number">2.2.2.2.</span> <span class="post-toc-text">3.2.2 DOUBLE</span></a></li><li class="post-toc-item post-toc-level-4"><a class="post-toc-link" href="#3-2-3-DECIMAL"><span class="post-toc-number">2.2.2.3.</span> <span class="post-toc-text">3.2.3 DECIMAL</span></a></li><li class="post-toc-item post-toc-level-4"><a class="post-toc-link" href="#测试浮点型"><span class="post-toc-number">2.2.2.4.</span> <span class="post-toc-text">测试浮点型</span></a></li></ol></li><li class="post-toc-item post-toc-level-3"><a class="post-toc-link" href="#3-3-字符型"><span class="post-toc-number">2.2.3.</span> <span class="post-toc-text">3.3 字符型</span></a><ol class="post-toc-child"><li class="post-toc-item post-toc-level-4"><a class="post-toc-link" href="#3-3-1-CHAR-定长字符"><span class="post-toc-number">2.2.3.1.</span> <span class="post-toc-text">3.3.1 CHAR 定长字符</span></a></li><li class="post-toc-item post-toc-level-4"><a class="post-toc-link" href="#3-3-2-VARCHAR-变长字符"><span class="post-toc-number">2.2.3.2.</span> <span class="post-toc-text">3.3.2 VARCHAR 变长字符</span></a></li><li class="post-toc-item post-toc-level-4"><a class="post-toc-link" href="#3-3-3-TINYTEXT"><span class="post-toc-number">2.2.3.3.</span> <span class="post-toc-text">3.3.3 TINYTEXT</span></a></li><li class="post-toc-item post-toc-level-4"><a class="post-toc-link" href="#3-3-4-TEXT"><span class="post-toc-number">2.2.3.4.</span> <span class="post-toc-text">3.3.4 TEXT</span></a></li><li class="post-toc-item post-toc-level-4"><a class="post-toc-link" href="#3-3-5-MEDIUMTEXT"><span class="post-toc-number">2.2.3.5.</span> <span class="post-toc-text">3.3.5 MEDIUMTEXT</span></a></li><li class="post-toc-item post-toc-level-4"><a class="post-toc-link" href="#3-3-6-ENUM"><span class="post-toc-number">2.2.3.6.</span> <span class="post-toc-text">3.3.6 ENUM</span></a></li><li class="post-toc-item post-toc-level-4"><a class="post-toc-link" href="#3-3-7-SET"><span class="post-toc-number">2.2.3.7.</span> <span class="post-toc-text">3.3.7 SET</span></a></li><li class="post-toc-item post-toc-level-4"><a class="post-toc-link" href="#测试字符"><span class="post-toc-number">2.2.3.8.</span> <span class="post-toc-text">测试字符</span></a></li><li class="post-toc-item post-toc-level-4"><a class="post-toc-link" href="#测试枚举类型"><span class="post-toc-number">2.2.3.9.</span> <span class="post-toc-text">测试枚举类型</span></a></li><li class="post-toc-item post-toc-level-4"><a class="post-toc-link" href="#测试集合类型"><span class="post-toc-number">2.2.3.10.</span> <span class="post-toc-text">测试集合类型</span></a></li></ol></li><li class="post-toc-item post-toc-level-3"><a class="post-toc-link" href="#3-4-日期时间型"><span class="post-toc-number">2.2.4.</span> <span class="post-toc-text">3.4 日期时间型</span></a><ol class="post-toc-child"><li class="post-toc-item post-toc-level-4"><a class="post-toc-link" href="#3-4-1-TIME"><span class="post-toc-number">2.2.4.1.</span> <span class="post-toc-text">3.4.1 TIME</span></a></li><li class="post-toc-item post-toc-level-4"><a class="post-toc-link" href="#3-4-2-DATTE"><span class="post-toc-number">2.2.4.2.</span> <span class="post-toc-text">3.4.2 DATTE</span></a></li><li class="post-toc-item post-toc-level-4"><a class="post-toc-link" href="#3-4-3-DATETIME"><span class="post-toc-number">2.2.4.3.</span> <span class="post-toc-text">3.4.3 DATETIME</span></a></li><li class="post-toc-item post-toc-level-4"><a class="post-toc-link" href="#3-4-4-TIMESTAMP"><span class="post-toc-number">2.2.4.4.</span> <span class="post-toc-text">3.4.4 TIMESTAMP</span></a></li><li class="post-toc-item post-toc-level-4"><a class="post-toc-link" href="#3-4-5-YEAR"><span class="post-toc-number">2.2.4.5.</span> <span class="post-toc-text">3.4.5 YEAR</span></a></li><li class="post-toc-item post-toc-level-4"><a class="post-toc-link" href="#测试时间类型"><span class="post-toc-number">2.2.4.6.</span> <span class="post-toc-text">测试时间类型</span></a></li></ol></li><li class="post-toc-item post-toc-level-3"><a class="post-toc-link" href="#3-5-BINARY"><span class="post-toc-number">2.2.5.</span> <span class="post-toc-text">3.5 BINARY</span></a></li><li class="post-toc-item post-toc-level-3"><a class="post-toc-link" href="#4-存储引擎"><span class="post-toc-number">2.2.6.</span> <span class="post-toc-text">4. 存储引擎</span></a><ol class="post-toc-child"><li class="post-toc-item post-toc-level-4"><a class="post-toc-link" href="#4-1-查看支持的存储引擎"><span class="post-toc-number">2.2.6.1.</span> <span class="post-toc-text">4.1 查看支持的存储引擎</span></a></li></ol></li></ol></li><li class="post-toc-item post-toc-level-2"><a class="post-toc-link" href="#4-表的操作"><span class="post-toc-number">2.3.</span> <span class="post-toc-text">4  表的操作</span></a><ol class="post-toc-child"><li class="post-toc-item post-toc-level-3"><a class="post-toc-link" href="#4-1-创建表"><span class="post-toc-number">2.3.1.</span> <span class="post-toc-text">4.1 创建表</span></a><ol class="post-toc-child"><li class="post-toc-item post-toc-level-5"><a class="post-toc-link" href="#语法"><span class="post-toc-number">2.3.1.0.1.</span> <span class="post-toc-text">语法</span></a></li><li class="post-toc-item post-toc-level-5"><a class="post-toc-link" href="#小练习"><span class="post-toc-number">2.3.1.0.2.</span> <span class="post-toc-text">小练习</span></a></li></ol></li></ol></li><li class="post-toc-item post-toc-level-3"><a class="post-toc-link" href="#4-2-查看表"><span class="post-toc-number">2.3.2.</span> <span class="post-toc-text">4.2 查看表</span></a><ol class="post-toc-child"><li class="post-toc-item post-toc-level-4"><a class="post-toc-link" href="#4-2-1-查看表结构"><span class="post-toc-number">2.3.2.1.</span> <span class="post-toc-text">4.2.1 查看表结构</span></a></li></ol></li><li class="post-toc-item post-toc-level-3"><a class="post-toc-link" href="#4-3-INSERT"><span class="post-toc-number">2.3.3.</span> <span class="post-toc-text">4.3 INSERT</span></a></li><li class="post-toc-item post-toc-level-3"><a class="post-toc-link" href="#4-4-表结构修改"><span class="post-toc-number">2.3.4.</span> <span class="post-toc-text">4.4  表结构修改</span></a><ol class="post-toc-child"><li class="post-toc-item post-toc-level-4"><a class="post-toc-link" href="#4-4-1-重命名表"><span class="post-toc-number">2.3.4.1.</span> <span class="post-toc-text">4.4.1 重命名表</span></a></li><li class="post-toc-item post-toc-level-4"><a class="post-toc-link" href="#4-4-2-添加字段"><span class="post-toc-number">2.3.4.2.</span> <span class="post-toc-text">4.4.2 添加字段</span></a></li><li class="post-toc-item post-toc-level-4"><a class="post-toc-link" href="#4-4-3-删除字段"><span class="post-toc-number">2.3.4.3.</span> <span class="post-toc-text">4.4.3 删除字段</span></a></li><li class="post-toc-item post-toc-level-4"><a class="post-toc-link" href="#4-4-4-修改字段"><span class="post-toc-number">2.3.4.4.</span> <span class="post-toc-text">4.4.4 修改字段</span></a></li><li class="post-toc-item post-toc-level-4"><a class="post-toc-link" href="#4-4-5-添加删除默认值"><span class="post-toc-number">2.3.4.5.</span> <span class="post-toc-text">4.4.5 添加删除默认值</span></a></li><li class="post-toc-item post-toc-level-4"><a class="post-toc-link" href="#4-4-6-添加删除主键"><span class="post-toc-number">2.3.4.6.</span> <span class="post-toc-text">4.4.6 添加删除主键</span></a></li><li class="post-toc-item post-toc-level-4"><a class="post-toc-link" href="#4-4-7-添加删除唯一索引"><span class="post-toc-number">2.3.4.7.</span> <span class="post-toc-text">4.4.7 添加删除唯一索引</span></a></li><li class="post-toc-item post-toc-level-4"><a class="post-toc-link" href="#4-4-8-修改表存储引擎"><span class="post-toc-number">2.3.4.8.</span> <span class="post-toc-text">4.4.8 修改表存储引擎</span></a></li><li class="post-toc-item post-toc-level-4"><a class="post-toc-link" href="#4-4-9-修改自动增长值"><span class="post-toc-number">2.3.4.9.</span> <span class="post-toc-text">4.4.9 修改自动增长值</span></a></li></ol></li><li class="post-toc-item post-toc-level-3"><a class="post-toc-link" href="#待整理-完整性约束"><span class="post-toc-number">2.3.5.</span> <span class="post-toc-text">待整理-完整性约束</span></a><ol class="post-toc-child"><li class="post-toc-item post-toc-level-4"><a class="post-toc-link" href="#主键约束-PRIMARY-KEY"><span class="post-toc-number">2.3.5.1.</span> <span class="post-toc-text">主键约束 PRIMARY KEY</span></a></li><li class="post-toc-item post-toc-level-4"><a class="post-toc-link" href="#自增长-AUTO-INCREMENT"><span class="post-toc-number">2.3.5.2.</span> <span class="post-toc-text">自增长 AUTO INCREMENT</span></a></li><li class="post-toc-item post-toc-level-4"><a class="post-toc-link" href="#非空约束"><span class="post-toc-number">2.3.5.3.</span> <span class="post-toc-text">非空约束</span></a></li><li class="post-toc-item post-toc-level-4"><a class="post-toc-link" href="#默认值-DEFAULT"><span class="post-toc-number">2.3.5.4.</span> <span class="post-toc-text">默认值 DEFAULT</span></a></li><li class="post-toc-item post-toc-level-4"><a class="post-toc-link" href="#唯一性约束"><span class="post-toc-number">2.3.5.5.</span> <span class="post-toc-text">唯一性约束</span></a></li></ol></li></ol></li><li class="post-toc-item post-toc-level-2"><a class="post-toc-link" href="#收回权限"><span class="post-toc-number">2.4.</span> <span class="post-toc-text">收回权限</span></a></li><li class="post-toc-item post-toc-level-2"><a class="post-toc-link" href="#查看权限"><span class="post-toc-number">2.5.</span> <span class="post-toc-text">查看权限</span></a></li><li class="post-toc-item post-toc-level-2"><a class="post-toc-link" href="#授权"><span class="post-toc-number">2.6.</span> <span class="post-toc-text">授权</span></a></li><li class="post-toc-item post-toc-level-2"><a class="post-toc-link" href="#5-7"><span class="post-toc-number">2.7.</span> <span class="post-toc-text">5.7</span></a></li></ol></li></ol>
        </nav>
    </aside>
    
<article id="post-41-mysql基础使用"
  class="post-article article-type-post fade" itemprop="blogPost">

    <div class="post-card">
        <h1 class="post-card-title">mysql基础使用</h1>
        <div class="post-meta">
            <time class="post-time" title="2016-09-10 16:25:08" datetime="2016-09-10T08:25:08.000Z"  itemprop="datePublished">2016-09-10</time>

            


            
<span id="busuanzi_container_page_pv" title="文章总阅读量" style='display:none'>
    <i class="icon icon-eye icon-pr"></i><span id="busuanzi_value_page_pv"></span>
</span>


        </div>
        <div class="post-content" id="post-content" itemprop="postContent">
            <p><excerpt in="" index="" |="" 首页摘要=""><br><!-- 安装8888888888888888888888888888888888888888888888888888888888888 --></excerpt></p>
<p><the rest="" of="" contents="" |="" 余下全文=""></the></p>
<h1 id="1-安装"><a href="#1-安装" class="headerlink" title="1. 安装"></a>1. 安装</h1><h1 id="2-基本使用"><a href="#2-基本使用" class="headerlink" title="2. 基本使用"></a>2. 基本使用</h1><a id="more"></a>
<h3 id="提示符"><a href="#提示符" class="headerlink" title="提示符"></a>提示符</h3><p>常用格式:</p>
<table>
<thead>
<tr>
<th style="text-align:center">格式</th>
<th style="text-align:center">意义</th>
</tr>
</thead>
<tbody>
<tr>
<td style="text-align:center">\D</td>
<td style="text-align:center">完整日期格式</td>
</tr>
<tr>
<td style="text-align:center">\d</td>
<td style="text-align:center">显示当前数据库</td>
</tr>
<tr>
<td style="text-align:center">\h</td>
<td style="text-align:center">服务器名</td>
</tr>
<tr>
<td style="text-align:center">\u</td>
<td style="text-align:center">当前用户名</td>
</tr>
</tbody>
</table>
<p>1.登入时指定<br>mysql –prompt=zrd<br><figure class="highlight plain"><table><tr><td class="code"><pre><div class="line">Welcome to the MySQL monitor.  Commands end with ; or \g.</div><div class="line">Your MySQL connection id is 15404</div><div class="line">Server version: 5.6.31-77.0-log Percona Server (GPL), Release 77.0, Revision 5c1061c</div><div class="line"></div><div class="line">Copyright (c) 2009-2016 Percona LLC and/or its affiliates</div><div class="line">Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.</div><div class="line"></div><div class="line">Oracle is a registered trademark of Oracle Corporation and/or its</div><div class="line">affiliates. Other names may be trademarks of their respective</div><div class="line">owners.</div><div class="line"></div><div class="line">Type &apos;help;&apos; or &apos;\h&apos; for help. Type &apos;\c&apos; to clear the current input statement.</div><div class="line"></div><div class="line">zrd</div></pre></td></tr></table></figure></p>
<p>2.在线修改格式<br><figure class="highlight plain"><table><tr><td class="code"><pre><div class="line">PROMPT &apos;\h=\u=\D=\d&apos;</div><div class="line">PROMPT set to &apos;&apos;\h=\u=\D=\d&apos;&apos;</div><div class="line">&apos;localhost=root=Thu Aug 25 23:39:37 2016=(none)&apos;</div></pre></td></tr></table></figure></p>
<p>3.配置文件中指定</p>
<h3 id="一些基本函数"><a href="#一些基本函数" class="headerlink" title="一些基本函数"></a>一些基本函数</h3><table>
<thead>
<tr>
<th style="text-align:left">语句</th>
<th style="text-align:left">意义</th>
</tr>
</thead>
<tbody>
<tr>
<td style="text-align:left">SELECT VERSION ();</td>
<td style="text-align:left">版本</td>
</tr>
<tr>
<td style="text-align:left">SELECT USER ();</td>
<td style="text-align:left">当前登入的用户名主机名</td>
</tr>
<tr>
<td style="text-align:left">SELECT NOW();</td>
<td style="text-align:left">当前时间</td>
</tr>
<tr>
<td style="text-align:left">DELIMITER //</td>
<td style="text-align:left">修改默认分隔符为//，原来是’ ; ‘</td>
</tr>
<tr>
<td style="text-align:left">mysql&gt;\T /tmp/mysql<em>sql</em>.log</td>
<td style="text-align:left">Logging to file ‘/tmp/mysql<em>sql</em>.log’ 将此会话所执行的语句及结果全部保存到指定的文件中</td>
</tr>
<tr>
<td style="text-align:left">\t</td>
<td style="text-align:left">mysql&gt;\t  Outfile disabled. 取消保存执行语句记录到文件中</td>
</tr>
</tbody>
</table>
<h3 id="注释"><a href="#注释" class="headerlink" title="注释"></a>注释</h3><p>注释的内容将不被执行</p>
<figure class="highlight plain"><table><tr><td class="code"><pre><div class="line">方法1</div><div class="line"># 注释的内容</div><div class="line">方法2</div><div class="line">-- 注释的内容</div></pre></td></tr></table></figure>
<h2 id="3-数据库相关操作-DDL数据库定义语言"><a href="#3-数据库相关操作-DDL数据库定义语言" class="headerlink" title="3.数据库相关操作(DDL数据库定义语言)"></a>3.数据库相关操作(DDL数据库定义语言)</h2><p>{} 花括号必须要写写上相关的内容<br>| 二选一<br>[] 方括号是可选的</p>
<h3 id="1-数据库创建"><a href="#1-数据库创建" class="headerlink" title="1.数据库创建"></a>1.数据库创建</h3><p>创建语法<br><figure class="highlight plain"><table><tr><td class="code"><pre><div class="line">CREATE &#123;DATABASE | SCHEMA&#125; [IF NOT EXISTS] db_name [create_specification]     [DEFAULT] CHARACTER SET [=] charset_name  | [DEFAULT] COLLATE [=] collation_name</div></pre></td></tr></table></figure></p>
<figure class="highlight plain"><table><tr><td class="code"><pre><div class="line">mysql&gt; CREATE DATABASE zrd_test;</div><div class="line">Query OK, 1 row affected (0.02 sec)</div><div class="line"></div><div class="line">mysql&gt; CREATE DATABASE IF NOT EXISTS zrd_test;</div><div class="line">Query OK, 1 row affected, 1 warning (0.01 sec)</div><div class="line"></div><div class="line">mysql&gt; SHOW WARNINGS ; #查看警告</div><div class="line">+-------+------+---------------------------------------------------+</div><div class="line">| Level | Code | Message                                           |</div><div class="line">+-------+------+---------------------------------------------------+</div><div class="line">| Note  | 1007 | Can&apos;t create database &apos;zrd_test&apos;; database exists |</div><div class="line">+-------+------+---------------------------------------------------+</div><div class="line">1 row in set (0.00 sec)</div><div class="line"></div><div class="line">指定字符编码</div><div class="line">mysql&gt; CREATE DATABASE IF NOT EXISTS zrd_test2 DEFAULT CHARACTER SET = &apos;GBK&apos;;</div><div class="line"></div><div class="line">mysql&gt; SHOW CREATE SCHEMA zrd_test2;</div><div class="line">+-----------+-------------------------------------------------------------------+</div><div class="line">| Database  | Create Database                                                   |</div><div class="line">+-----------+-------------------------------------------------------------------+</div><div class="line">| zrd_test2 | CREATE DATABASE `zrd_test2` /*!40100 DEFAULT CHARACTER SET gbk */ |</div><div class="line">+-----------+-------------------------------------------------------------------+</div></pre></td></tr></table></figure>
<h3 id="2-查看数据库"><a href="#2-查看数据库" class="headerlink" title="2.查看数据库"></a>2.查看数据库</h3><p>语法<br><figure class="highlight plain"><table><tr><td class="code"><pre><div class="line">SHOW &#123;DATABASES | SCHEMAS&#125; [like_or_where]</div></pre></td></tr></table></figure></p>
<figure class="highlight plain"><table><tr><td class="code"><pre><div class="line">mysql&gt; SHOW DATABASES;</div><div class="line">+--------------------+</div><div class="line">| Database           |</div><div class="line">+--------------------+</div><div class="line">| information_schema |</div><div class="line">| mysql              |</div><div class="line">| performance_schema |</div><div class="line">| test               |</div><div class="line">| zabbix             |</div><div class="line">| zrd_test           |</div><div class="line">+--------------------+</div><div class="line">6 rows in set (0.00 sec)</div></pre></td></tr></table></figure>
<h3 id="3-查看指定数据库的定义"><a href="#3-查看指定数据库的定义" class="headerlink" title="3. 查看指定数据库的定义"></a>3. 查看指定数据库的定义</h3><p>语法<br><figure class="highlight plain"><table><tr><td class="code"><pre><div class="line">SHOW CREATE &#123;DATABASE | SCHEMA&#125; [IF NOT EXISTS] db_name</div></pre></td></tr></table></figure></p>
<figure class="highlight plain"><table><tr><td class="code"><pre><div class="line"># 方法1</div><div class="line">mysql&gt; SHOW CREATE DATABASE zabbix;</div><div class="line">+----------+-----------------------------------------------------------------+</div><div class="line">| Database | Create Database                                                 |</div><div class="line">+----------+-----------------------------------------------------------------+</div><div class="line">| zabbix   | CREATE DATABASE `zabbix` /*!40100 DEFAULT CHARACTER SET utf8 */ |</div><div class="line">+----------+-----------------------------------------------------------------+</div><div class="line">1 row in set (0.00 sec)</div><div class="line"></div><div class="line"># 方法2</div><div class="line">mysql&gt; SHOW CREATE SCHEMA zrd_test ;</div><div class="line">+----------+-------------------------------------------------------------------+</div><div class="line">| Database | Create Database                                                   |</div><div class="line">+----------+-------------------------------------------------------------------+</div><div class="line">| zrd_test | CREATE DATABASE `zrd_test` /*!40100 DEFAULT CHARACTER SET utf8 */ |</div><div class="line">+----------+-------------------------------------------------------------------+</div><div class="line">1 row in set (0.00 sec)</div></pre></td></tr></table></figure>
<h3 id="4-修改数据库的编码方式"><a href="#4-修改数据库的编码方式" class="headerlink" title="4.修改数据库的编码方式"></a>4.修改数据库的编码方式</h3><p>语法<br><figure class="highlight plain"><table><tr><td class="code"><pre><div class="line">ALTER &#123;DATABASE | SCHEMA&#125; [db_name]</div><div class="line">    alter_specification ...</div><div class="line">ALTER &#123;DATABASE | SCHEMA&#125; db_name</div><div class="line">    UPGRADE DATA DIRECTORY NAME</div><div class="line"></div><div class="line">alter_specification:</div><div class="line">    [DEFAULT] CHARACTER SET [=] charset_name</div><div class="line">  | [DEFAULT] COLLATE [=] collation_name</div></pre></td></tr></table></figure></p>
<figure class="highlight plain"><table><tr><td class="code"><pre><div class="line">mysql&gt; ALTER DATABASE zrd_test2 DEFAULT CHARACTER SET=utf8;</div><div class="line">Query OK, 1 row affected (0.02 sec)</div><div class="line"></div><div class="line"></div><div class="line">mysql&gt; SHOW CREATE DATABASE zrd_test2;</div><div class="line">+-----------+--------------------------------------------------------------------+</div><div class="line">| Database  | Create Database                                                    |</div><div class="line">+-----------+--------------------------------------------------------------------+</div><div class="line">| zrd_test2 | CREATE DATABASE `zrd_test2` /*!40100 DEFAULT CHARACTER SET utf8 */ |</div><div class="line">+-----------+--------------------------------------------------------------------+</div><div class="line">1 row in set (0.00 sec)</div></pre></td></tr></table></figure>
<h3 id="5-切换数据库"><a href="#5-切换数据库" class="headerlink" title="5.切换数据库"></a>5.切换数据库</h3><figure class="highlight plain"><table><tr><td class="code"><pre><div class="line">mysql&gt; USE zrd_test2; # 切换数据库</div><div class="line">Database changed</div><div class="line">mysql&gt; SELECT DATABASE(); # 显示当前正在使用的数据库</div><div class="line">+------------+</div><div class="line">| DATABASE() |</div><div class="line">+------------+</div><div class="line">| zrd_test2  |</div><div class="line">+------------+</div><div class="line">1 row in set (0.00 sec)</div></pre></td></tr></table></figure>
<h3 id="6-删除数据库"><a href="#6-删除数据库" class="headerlink" title="6.删除数据库"></a>6.删除数据库</h3><p>语法:<br><figure class="highlight plain"><table><tr><td class="code"><pre><div class="line">DROP &#123;DATABASE | SCHEMA&#125; [IF EXISTS] db_name</div></pre></td></tr></table></figure></p>
<figure class="highlight plain"><table><tr><td class="code"><pre><div class="line">mysql&gt; DROP DATABASE zrd_test2;</div><div class="line">Query OK, 0 rows affected (0.03 sec)</div></pre></td></tr></table></figure>
<h2 id="3-mysql-数据类型"><a href="#3-mysql-数据类型" class="headerlink" title="3. mysql 数据类型"></a>3. mysql 数据类型</h2><h3 id="3-1-整数型"><a href="#3-1-整数型" class="headerlink" title="3.1 整数型"></a>3.1 整数型</h3><figure class="image-bubble">
                <div class="img-lightbox">
                    <div class="overlay"></div>
                    <img src="https://raw.githubusercontent.com/cucyzrd/file/master/num.png" alt="" title="">
                </div>
                <div class="image-caption"></div>
            </figure>
<h4 id="3-1-1-TINYINT"><a href="#3-1-1-TINYINT" class="headerlink" title="3.1.1 TINYINT"></a>3.1.1 TINYINT</h4><figure class="highlight plain"><table><tr><td class="code"><pre><div class="line">Name: &apos;TINYINT&apos;</div><div class="line">Description:</div><div class="line">TINYINT[(M)] [UNSIGNED] [ZEROFILL]</div><div class="line">A very small integer. The signed range is -128 to 127. The unsigned range is 0 to 255.</div></pre></td></tr></table></figure>
<h4 id="3-1-2-SMALLINT"><a href="#3-1-2-SMALLINT" class="headerlink" title="3.1.2 SMALLINT"></a>3.1.2 SMALLINT</h4><figure class="highlight plain"><table><tr><td class="code"><pre><div class="line">Name: &apos;SMALLINT&apos;</div><div class="line">Description:</div><div class="line">SMALLINT[(M)] [UNSIGNED] [ZEROFILL]</div><div class="line">A small integer. The signed range is -32768 to 32767. The unsigned range is 0 to 65535.</div></pre></td></tr></table></figure>
<h4 id="3-1-3-MEDIUMINT"><a href="#3-1-3-MEDIUMINT" class="headerlink" title="3.1.3 MEDIUMINT"></a>3.1.3 MEDIUMINT</h4><figure class="highlight plain"><table><tr><td class="code"><pre><div class="line">Name: &apos;MEDIUMINT&apos;</div><div class="line">Description:</div><div class="line">MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]</div><div class="line">A medium-sized integer. The signed range is -8388608 to 8388607. The unsigned range is 0 to 16777215.</div></pre></td></tr></table></figure>
<h4 id="3-1-4-INT"><a href="#3-1-4-INT" class="headerlink" title="3.1.4 INT"></a>3.1.4 INT</h4><figure class="highlight plain"><table><tr><td class="code"><pre><div class="line">Name: &apos;INT&apos;</div><div class="line">Description:</div><div class="line">INT[(M)] [UNSIGNED] [ZEROFILL]</div><div class="line">A normal-size integer. The signed range is -2147483648 to 2147483647. The unsigned range is 0 to 4294967295.</div></pre></td></tr></table></figure>
<h4 id="3-1-5-BIGINT"><a href="#3-1-5-BIGINT" class="headerlink" title="3.1.5 BIGINT"></a>3.1.5 BIGINT</h4><figure class="highlight plain"><table><tr><td class="code"><pre><div class="line">--&gt;? BIGINT</div><div class="line">Name: &apos;BIGINT&apos;</div><div class="line">Description:</div><div class="line">BIGINT[(M)] [UNSIGNED] [ZEROFILL]</div><div class="line">A large integer. The signed range is -9223372036854775808 to 9223372036854775807. The unsigned range is 0 to 18446744073709551615.</div></pre></td></tr></table></figure>
<h4 id="3-1-6-BOOLEAN-BOOL"><a href="#3-1-6-BOOLEAN-BOOL" class="headerlink" title="3.1.6 BOOLEAN BOOL"></a>3.1.6 BOOLEAN BOOL</h4><figure class="highlight plain"><table><tr><td class="code"><pre><div class="line">Name: &apos;BOOLEAN&apos;</div><div class="line">Description:</div><div class="line">BOOL, BOOLEAN</div><div class="line">These types are synonyms for TINYINT(1). A value of zero is considered false. Nonzero values are considered true:</div></pre></td></tr></table></figure>
<h4 id="测试整型"><a href="#测试整型" class="headerlink" title="测试整型"></a>测试整型</h4><figure class="highlight plain"><table><tr><td class="code"><pre><div class="line">-- 测试整型</div><div class="line">CREATE TABLE IF NOT EXISTS `num`(</div><div class="line">`num1` TINYINT  COMMENT &apos;最小的整型&apos;,</div><div class="line">`num2` SMALLINT,</div><div class="line">`num3` MEDIUMINT,</div><div class="line">`num4` INT,</div><div class="line">`num5` BIGINT</div><div class="line">) CHARSET=GB2312;</div><div class="line"></div><div class="line">percona root@localhost:test&gt; DESC num;</div><div class="line">+---------+--------------+--------+-------+-----------+---------+</div><div class="line">| Field   | Type         | Null   | Key   |   Default | Extra   |</div><div class="line">|---------+--------------+--------+-------+-----------+---------|</div><div class="line">| num1    | tinyint(4)   | YES    |       |    &lt;null&gt; |         |</div><div class="line">| num2    | smallint(6)  | YES    |       |    &lt;null&gt; |         |</div><div class="line">| num3    | mediumint(9) | YES    |       |    &lt;null&gt; |         |</div><div class="line">| num4    | int(11)      | YES    |       |    &lt;null&gt; |         |</div><div class="line">| num5    | bigint(20)   | YES    |       |    &lt;null&gt; |         |</div><div class="line">+---------+--------------+--------+-------+-----------+---------+</div><div class="line">5 rows in set</div><div class="line">Time: 0.003s</div><div class="line"># 插入数据</div><div class="line">INSERT INTO `num` VALUES(-128,-32768,-8388608,-2147483648,-9223372036854775808);</div><div class="line">Query OK, 1 row affected (0.02 sec)</div><div class="line"># 查询数据</div><div class="line">SELECT *  FROM `num`;</div><div class="line">+------+--------+----------+-------------+----------------------+</div><div class="line">| num1 | num2   | num3     | num4        | num5                 |</div><div class="line">+------+--------+----------+-------------+----------------------+</div><div class="line">| -128 | -32768 | -8388608 | -2147483648 | -9223372036854775808 |</div><div class="line">+------+--------+----------+-------------+----------------------+</div><div class="line">1 row in set (0.00 sec)</div><div class="line"># 插入错误不符合规范的数据</div><div class="line">INSERT INTO `num` VALUES(-129,-32768,-8388608,-2147483648,-9223372036854775808);</div><div class="line">Query OK, 1 row affected, 1 warning (0.02 sec)</div><div class="line"># 可发现插入不规则的语句后有提示</div><div class="line">--&gt;SHOW warnings ;</div><div class="line">+---------+------+-----------------------------------------------+</div><div class="line">| Level   | Code | Message                                       |</div><div class="line">+---------+------+-----------------------------------------------+</div><div class="line">| Warning | 1264 | Out of range value for column &apos;num1&apos; at row 1 |</div><div class="line">+---------+------+-----------------------------------------------+</div><div class="line">1 row in set (0.01 sec)</div><div class="line">--&gt;SELECT * FROM num;</div><div class="line">+------+--------+----------+-------------+----------------------+</div><div class="line">| num1 | num2   | num3     | num4        | num5                 |</div><div class="line">+------+--------+----------+-------------+----------------------+</div><div class="line">| -128 | -32768 | -8388608 | -2147483648 | -9223372036854775808 |</div><div class="line">| -128 | -32768 | -8388608 | -2147483648 | -9223372036854775808 |</div><div class="line">+------+--------+----------+-------------+----------------------+</div><div class="line">2 rows in set (0.00 sec)</div><div class="line"></div><div class="line"># 无符号整型</div><div class="line">-- 插入无符号整型 UNSIGNED</div><div class="line">CREATE TABLE `num2`(</div><div class="line">num1 TINYINT UNSIGNED COMMENT &apos;UNSIGNED 是无符号的,计数是从0开始&apos;,</div><div class="line">num2 TINYINT </div><div class="line">) CHARSET=GB2312;</div><div class="line"># 查看表结构，发现多了一个unsigned</div><div class="line">--&gt;DESC num2;</div><div class="line">+-------+---------------------+------+-----+---------+-------+</div><div class="line">| Field | Type                | Null | Key | Default | Extra |</div><div class="line">+-------+---------------------+------+-----+---------+-------+</div><div class="line">| num1  | tinyint(3) unsigned | YES  |     | NULL    |       |</div><div class="line">| num2  | tinyint(4)          | YES  |     | NULL    |       |</div><div class="line">+-------+---------------------+------+-----+---------+-------+</div><div class="line">2 rows in set (0.00 sec)</div><div class="line"># 插入一个正常的值</div><div class="line">--&gt;INSERT INTO num2 VALUES(0,-12);</div><div class="line">Query OK, 1 row affected (0.02 sec)</div><div class="line"></div><div class="line">--&gt;SELECT * FROM num2;</div><div class="line">+------+------+</div><div class="line">| num1 | num2 |</div><div class="line">+------+------+</div><div class="line">|    0 |  -12 |</div><div class="line">+------+------+</div><div class="line">1 row in set (0.00 sec)</div><div class="line">#  插入一个不符合的值</div><div class="line">--&gt;INSERT INTO num2 VALUES(-10,-12);</div><div class="line">ERROR 1264 (22003): Out of range value for column &apos;num1&apos; at row 1</div><div class="line"></div><div class="line"></div><div class="line"># 显示正常的长度 ZEROFILL , 它会设置成无符号，0填充</div><div class="line">CREATE TABLE IF NOT EXISTS `num3` (</div><div class="line">`num1` TINYINT  ZEROFILL COMMENT &apos;最小的整型&apos;,</div><div class="line">`num2` SMALLINT ZEROFILL,</div><div class="line">`num3` MEDIUMINT ZEROFILL,</div><div class="line">`num4` INT ZEROFILL,</div><div class="line">`num5` BIGINT ZEROFILL</div><div class="line">) CHARSET=GB2312;</div><div class="line"></div><div class="line">--&gt;DESC num3;</div><div class="line">+-------+--------------------------------+------+-----+---------+-------+</div><div class="line">| Field | Type                           | Null | Key | Default | Extra |</div><div class="line">+-------+--------------------------------+------+-----+---------+-------+</div><div class="line">| num1  | tinyint(3) unsigned zerofill   | YES  |     | NULL    |       |</div><div class="line">| num2  | smallint(5) unsigned zerofill  | YES  |     | NULL    |       |</div><div class="line">| num3  | mediumint(8) unsigned zerofill | YES  |     | NULL    |       |</div><div class="line">| num4  | int(10) unsigned zerofill      | YES  |     | NULL    |       |</div><div class="line">| num5  | bigint(20) unsigned zerofill   | YES  |     | NULL    |       |</div><div class="line">+-------+--------------------------------+------+-----+---------+-------+</div><div class="line">5 rows in set (0.00 sec)</div><div class="line"># 插入数据查看效果</div><div class="line">--&gt;INSERT INTO `num3` VALUES(1,1,1,1,1);</div><div class="line">Query OK, 1 row affected (0.02 sec)</div><div class="line"></div><div class="line"># 可以发现没有达到长度的会在前边补0(当做最大值来记录)   一般没用，美观</div><div class="line">--&gt;SELECT * FROM num3;</div><div class="line">+------+-------+----------+------------+----------------------+</div><div class="line">| num1 | num2  | num3     | num4       | num5                 |</div><div class="line">+------+-------+----------+------------+----------------------+</div><div class="line">|  001 | 00001 | 00000001 | 0000000001 | 00000000000000000001 |</div><div class="line">+------+-------+----------+------------+----------------------+</div><div class="line">1 row in set (0.00 sec)</div><div class="line"># 当达到长度后,不会再补0</div></pre></td></tr></table></figure>
<h4 id="无符号整型-UNSIGNED"><a href="#无符号整型-UNSIGNED" class="headerlink" title="无符号整型 UNSIGNED"></a>无符号整型 UNSIGNED</h4><h4 id="零填充ZEROFILL"><a href="#零填充ZEROFILL" class="headerlink" title="零填充ZEROFILL"></a>零填充ZEROFILL</h4><h3 id="3-2-浮点型"><a href="#3-2-浮点型" class="headerlink" title="3.2 浮点型"></a>3.2 浮点型</h3><figure class="image-bubble">
                <div class="img-lightbox">
                    <div class="overlay"></div>
                    <img src="https://raw.githubusercontent.com/cucyzrd/file/master/float.png" alt="" title="">
                </div>
                <div class="image-caption"></div>
            </figure>
<h4 id="3-2-1-FLOAT"><a href="#3-2-1-FLOAT" class="headerlink" title="3.2.1 FLOAT"></a>3.2.1 FLOAT</h4><figure class="highlight plain"><table><tr><td class="code"><pre><div class="line">Name: &apos;FLOAT&apos;</div><div class="line">Description:</div><div class="line">FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]</div><div class="line"></div><div class="line">A small (single-precision) floating-point number. Permissible values</div><div class="line">are -3.402823466E+38 to -1.175494351E-38, 0, and 1.175494351E-38 to</div><div class="line">3.402823466E+38. These are the theoretical limits, based on the IEEE</div><div class="line">standard. The actual range might be slightly smaller depending on your</div><div class="line">hardware or operating system.</div><div class="line"># 占用4个字节，单精度浮点型</div></pre></td></tr></table></figure>
<h4 id="3-2-2-DOUBLE"><a href="#3-2-2-DOUBLE" class="headerlink" title="3.2.2 DOUBLE"></a>3.2.2 DOUBLE</h4><figure class="highlight plain"><table><tr><td class="code"><pre><div class="line">Name: &apos;DOUBLE&apos;</div><div class="line">Description:</div><div class="line">DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]</div><div class="line"></div><div class="line">A normal-size (double-precision) floating-point number. Permissible</div><div class="line">values are -1.7976931348623157E+308 to -2.2250738585072014E-308, 0, and</div><div class="line">2.2250738585072014E-308 to 1.7976931348623157E+308. These are the</div><div class="line">theoretical limits, based on the IEEE standard. The actual range might</div><div class="line">be slightly smaller depending on your hardware or operating system.</div><div class="line"># 双精度浮点型</div></pre></td></tr></table></figure>
<h4 id="3-2-3-DECIMAL"><a href="#3-2-3-DECIMAL" class="headerlink" title="3.2.3 DECIMAL"></a>3.2.3 DECIMAL</h4><figure class="highlight plain"><table><tr><td class="code"><pre><div class="line">Name: &apos;DECIMAL&apos;</div><div class="line">Description:</div><div class="line">DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]</div><div class="line"></div><div class="line">A packed &quot;exact&quot; fixed-point number. M is the total number of digits</div><div class="line">(the precision) and D is the number of digits after the decimal point</div><div class="line">(the scale). The decimal point and (for negative numbers) the &quot;-&quot; sign</div><div class="line">are not counted in M. If D is 0, values have no decimal point or</div><div class="line">fractional part. The maximum number of digits (M) for DECIMAL is 65.</div><div class="line">The maximum number of supported decimals (D) is 30. If D is omitted,</div><div class="line">the default is 0. If M is omitted, the default is 10.</div><div class="line"># 占用的长度为字节长度+2 和float一样</div></pre></td></tr></table></figure>
<h4 id="测试浮点型"><a href="#测试浮点型" class="headerlink" title="测试浮点型"></a>测试浮点型</h4><figure class="highlight plain"><table><tr><td class="code"><pre><div class="line">-- 测试浮点型</div><div class="line">CREATE TABLE IF NOT EXISTS `num4` (</div><div class="line">num1 FLOAT(6,2),</div><div class="line">num2 DOUBLE(6,2),</div><div class="line">num3 DECIMAL(6,2) COMMENT &apos;定点数不进行四舍五入,对精度要求比较高&apos;</div><div class="line">) CHARSET=GB2312;</div><div class="line"></div><div class="line">--&gt;DESC num4;</div><div class="line">+-------+--------------+------+-----+---------+-------+</div><div class="line">| Field | Type         | Null | Key | Default | Extra |</div><div class="line">+-------+--------------+------+-----+---------+-------+</div><div class="line">| num1  | float(6,2)   | YES  |     | NULL    |       |</div><div class="line">| num2  | double(6,2)  | YES  |     | NULL    |       |</div><div class="line">| num3  | decimal(6,2) | YES  |     | NULL    |       |</div><div class="line">+-------+--------------+------+-----+---------+-------+</div><div class="line">3 rows in set (0.00 sec)</div><div class="line"></div><div class="line"># 插入值测试</div><div class="line">INSERT INTO `num4` VALUES(3.141526, 3.141526, 3.141526);</div><div class="line">Query OK, 1 row affected, 1 warning (0.03 sec)</div><div class="line"># 警告信息, 查看插入的数据被四舍五入截断</div><div class="line">--&gt;SHOW WARNINGS ;</div><div class="line">+-------+------+-------------------------------------------+</div><div class="line">| Level | Code | Message                                   |</div><div class="line">+-------+------+-------------------------------------------+</div><div class="line">| Note  | 1265 | Data truncated for column &apos;num3&apos; at row 1 |</div><div class="line">+-------+------+-------------------------------------------+</div><div class="line">1 row in set (0.00 sec)</div><div class="line">--&gt;SELECT * FROM num4;</div><div class="line">+------+------+------+</div><div class="line">| num1 | num2 | num3 |</div><div class="line">+------+------+------+</div><div class="line">| 3.14 | 3.14 | 3.14 |</div><div class="line">+------+------+------+</div><div class="line">1 row in set (0.00 sec)</div><div class="line"></div><div class="line"># 验证</div><div class="line">INSERT INTO `num4` VALUES(2.149526, 2.149526, 2.149526);</div><div class="line">Query OK, 1 row affected, 1 warning (0.01 sec)</div><div class="line"></div><div class="line">root@localhost Sat Aug 27 21:31:02 2016 [test] </div><div class="line">--&gt;SELECT * FROM num4;</div><div class="line">+------+------+------+</div><div class="line">| num1 | num2 | num3 |</div><div class="line">+------+------+------+</div><div class="line">| 3.14 | 3.14 | 3.14 |</div><div class="line">| 2.15 | 2.15 | 2.15 |</div><div class="line">+------+------+------+</div><div class="line">2 rows in set (0.00 sec)</div></pre></td></tr></table></figure>
<h3 id="3-3-字符型"><a href="#3-3-字符型" class="headerlink" title="3.3 字符型"></a>3.3 字符型</h3><figure class="image-bubble">
                <div class="img-lightbox">
                    <div class="overlay"></div>
                    <img src="https://raw.githubusercontent.com/cucyzrd/file/master/char.png" alt="" title="">
                </div>
                <div class="image-caption"></div>
            </figure>
<h4 id="3-3-1-CHAR-定长字符"><a href="#3-3-1-CHAR-定长字符" class="headerlink" title="3.3.1 CHAR 定长字符"></a>3.3.1 CHAR 定长字符</h4><p>速度快</p>
<figure class="highlight plain"><table><tr><td class="code"><pre><div class="line">--&gt;? char</div><div class="line">Name: &apos;CHAR&apos;</div><div class="line">Description:</div><div class="line">[NATIONAL] CHAR[(M)] [CHARACTER SET charset_name] [COLLATE collation_name]</div><div class="line"></div><div class="line">A fixed-length string that is always right-padded with spaces to the specified length when stored. M represents the column length in characters. The range of M is 0 to 255. If M is omitted, the length is 1.</div><div class="line"># M个字节，0&lt;=&lt;=255</div></pre></td></tr></table></figure>
<h4 id="3-3-2-VARCHAR-变长字符"><a href="#3-3-2-VARCHAR-变长字符" class="headerlink" title="3.3.2 VARCHAR 变长字符"></a>3.3.2 VARCHAR 变长字符</h4><p>速度比较慢</p>
<figure class="highlight plain"><table><tr><td class="code"><pre><div class="line">Name: &apos;VARCHAR&apos;</div><div class="line">Description:</div><div class="line">[NATIONAL] VARCHAR(M) [CHARACTER SET charset_name] [COLLATE collation_name ]</div><div class="line"></div><div class="line">A variable-length string. M represents the maximum column length in characters. The range of M is 0 to 65,535. The effective maximum length of a VARCHAR is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used. For example, utf8 characters can require up to three bytes per character, so a VARCHAR column that uses the utf8 character set can be declared to be a maximum of 21,844 characters.</div><div class="line"># L+1字节，其中L&lt;=M且0&lt;=M&lt;=65535</div></pre></td></tr></table></figure>
<h4 id="3-3-3-TINYTEXT"><a href="#3-3-3-TINYTEXT" class="headerlink" title="3.3.3 TINYTEXT"></a>3.3.3 TINYTEXT</h4><figure class="highlight plain"><table><tr><td class="code"><pre><div class="line">Name: &apos;TINYTEXT&apos;</div><div class="line">Description:</div><div class="line">TINYTEXT [CHARACTER SET charset_name] [COLLATE collation_name]</div><div class="line"></div><div class="line">A TEXT column with a maximum length of 255 (28 - 1) characters. The effective maximum length is less if the value contains multi-byte characters. Each TINYTEXT value is stored using a 1-byte length prefix that indicates the number of bytes in the value.</div><div class="line"># L+1 个字节， L小于2的8次方</div></pre></td></tr></table></figure>
<h4 id="3-3-4-TEXT"><a href="#3-3-4-TEXT" class="headerlink" title="3.3.4 TEXT"></a>3.3.4 TEXT</h4><figure class="highlight plain"><table><tr><td class="code"><pre><div class="line">Name: &apos;TEXT&apos;</div><div class="line">Description:</div><div class="line">TEXT[(M)] [CHARACTER SET charset_name] [COLLATE collation_name]</div><div class="line"></div><div class="line">A TEXT column with a maximum length of 65,535 (216 - 1) characters. The</div><div class="line">effective maximum length is less if the value contains multi-byte</div><div class="line">characters. Each TEXT value is stored using a 2-byte length prefix that</div><div class="line">indicates the number of bytes in the value.</div><div class="line"># L+2 个字节， L小于2的16次方</div></pre></td></tr></table></figure>
<h4 id="3-3-5-MEDIUMTEXT"><a href="#3-3-5-MEDIUMTEXT" class="headerlink" title="3.3.5 MEDIUMTEXT"></a>3.3.5 MEDIUMTEXT</h4><figure class="highlight plain"><table><tr><td class="code"><pre><div class="line">Name: &apos;MEDIUMTEXT&apos;</div><div class="line">Description:</div><div class="line">MEDIUMTEXT [CHARACTER SET charset_name] [COLLATE collation_name]</div><div class="line"></div><div class="line">A TEXT column with a maximum length of 16,777,215 (224 - 1) characters.</div><div class="line">The effective maximum length is less if the value contains multi-byte</div><div class="line">characters. Each MEDIUMTEXT value is stored using a 3-byte length</div><div class="line">prefix that indicates the number of bytes in the value.</div><div class="line"># L+3 个字节， L小于2的24次方</div></pre></td></tr></table></figure>
<h4 id="3-3-6-ENUM"><a href="#3-3-6-ENUM" class="headerlink" title="3.3.6 ENUM"></a>3.3.6 ENUM</h4><figure class="highlight plain"><table><tr><td class="code"><pre><div class="line">Name: &apos;ENUM&apos;</div><div class="line">Description:</div><div class="line">ENUM(&apos;value1&apos;,&apos;value2&apos;,...) [CHARACTER SET charset_name] [COLLATE</div><div class="line">collation_name]</div><div class="line"></div><div class="line">An enumeration. A string object that can have only one value, chosen</div><div class="line">from the list of values &apos;value1&apos;, &apos;value2&apos;, ..., NULL or the special &apos;&apos;</div><div class="line">error value. ENUM values are represented internally as integers.</div><div class="line"># 枚举类型，列举中的字节</div></pre></td></tr></table></figure>
<h4 id="3-3-7-SET"><a href="#3-3-7-SET" class="headerlink" title="3.3.7 SET"></a>3.3.7 SET</h4><figure class="highlight plain"><table><tr><td class="code"><pre><div class="line">Name: &apos;SET&apos;</div><div class="line">Description:</div><div class="line">Syntax:</div><div class="line">SET variable_assignment [, variable_assignment] ...</div><div class="line"># 和枚举类似，但是集合同时可以选择多个值</div></pre></td></tr></table></figure>
<h4 id="测试字符"><a href="#测试字符" class="headerlink" title="测试字符"></a>测试字符</h4><figure class="highlight plain"><table><tr><td class="code"><pre><div class="line">-- 测试 CHAR 和 VCHAR</div><div class="line">CREATE TABLE IF NOT EXISTS `cha1`(</div><div class="line">`str1` CHAR(5),</div><div class="line">`str2` CHAR(5)</div><div class="line">);</div><div class="line">Query OK, 0 rows affected (0.06 sec)</div><div class="line"></div><div class="line">INSERT INTO cha1 VALUES(&apos;1&apos;,&apos;1&apos;);</div><div class="line">Query OK, 1 row affected (0.01 sec)</div><div class="line"># 插入超出存储范围</div><div class="line">INSERT INTO cha1 VALUES(&apos;123456&apos;,&apos;123456&apos;);</div><div class="line">--&gt;show WARNINGS ;</div><div class="line">+---------+------+-------------------------------------------+</div><div class="line">| Level   | Code | Message                                   |</div><div class="line">+---------+------+-------------------------------------------+</div><div class="line">| Warning | 1265 | Data truncated for column &apos;str1&apos; at row 1 |</div><div class="line">| Warning | 1265 | Data truncated for column &apos;str2&apos; at row 1 |</div><div class="line">+---------+------+-------------------------------------------+</div><div class="line">2 rows in set (0.00 sec)</div><div class="line"></div><div class="line"># CHAR 会把多余的空格去掉，而VCHAR会保留</div><div class="line"># 后边的会被去掉</div><div class="line">INSERT INTO cha1 VALUES(&apos;1  &apos;,&apos;1  &apos;);</div><div class="line"># 前边的都会保留</div><div class="line">INSERT INTO cha1 VALUES(&apos;  a&apos;,&apos;  a&apos;);</div><div class="line"></div><div class="line"># 插入中文</div><div class="line">--&gt;INSERT INTO cha1 VALUES(&apos;我&apos;,&apos;我&apos;);</div><div class="line">Query OK, 1 row affected (0.04 sec)</div><div class="line"></div><div class="line">--&gt;SELECT * FROM cha1;</div><div class="line">+------+------+</div><div class="line">| str1 | str2 |</div><div class="line">+------+------+</div><div class="line">| 1    | 1    |</div><div class="line">| 1    | 1    |</div><div class="line">|   a  |   a  |</div><div class="line">| 我   | 我   |</div><div class="line">+------+------+</div><div class="line">4 rows in set (0.00 sec)</div></pre></td></tr></table></figure>
<h4 id="测试枚举类型"><a href="#测试枚举类型" class="headerlink" title="测试枚举类型"></a>测试枚举类型</h4><figure class="highlight plain"><table><tr><td class="code"><pre><div class="line">-- 测试枚举类型</div><div class="line">CREATE TABLE IF NOT EXISTS `test7` (</div><div class="line">sex ENUM(&apos;男&apos;,&apos;女&apos;,&apos;保密&apos;) COMMENT &apos;枚举类型&apos;</div><div class="line">);</div><div class="line"></div><div class="line">INSERT INTO test7 VALUES(&apos;男&apos;);</div><div class="line">INSERT INTO test7 VALUES(&apos;女&apos;);</div><div class="line">INSERT INTO test7 VALUES(&apos;保密&apos;);</div><div class="line">SELECT * FROM test7;</div><div class="line">+--------+</div><div class="line">| sex    |</div><div class="line">+--------+</div><div class="line">| 男     |</div><div class="line">| 女     |</div><div class="line">| 保密   |</div><div class="line">+--------+</div><div class="line">3 rows in set (0.00 sec)</div><div class="line"></div><div class="line"></div><div class="line"># 插入不符合情况时报错</div><div class="line">INSERT INTO test7 VALUES(&apos;保密1&apos;);</div><div class="line">Query OK, 1 row affected, 1 warning (0.03 sec)</div><div class="line">--&gt;show warnings ;</div><div class="line">+---------+------+------------------------------------------+</div><div class="line">| Level   | Code | Message                                  |</div><div class="line">+---------+------+------------------------------------------+</div><div class="line">| Warning | 1265 | Data truncated for column &apos;sex&apos; at row 1 |</div><div class="line">+---------+------+------------------------------------------+</div><div class="line">1 row in set (0.00 sec)</div><div class="line">--&gt;SELECT * FROM test7;</div><div class="line">+--------+</div><div class="line">| sex    |</div><div class="line">+--------+</div><div class="line">| 男     |</div><div class="line">| 女     |</div><div class="line">| 保密   |</div><div class="line">|        |</div><div class="line">+--------+</div><div class="line"></div><div class="line"># 可选择对应的序列插入 1--&gt;男, 2--&gt;女 ， 3--&gt;保密  从1开始计数， 只能从列表中选择一个对象，可以插入空值NULL</div><div class="line">INSERT INTO test7 VALUES(&apos;1&apos;);</div><div class="line">--&gt;SELECT * FROM test7;</div><div class="line">+--------+</div><div class="line">| sex    |</div><div class="line">+--------+</div><div class="line">| 男     |</div><div class="line">| 女     |</div><div class="line">| 保密   |</div><div class="line">|        |</div><div class="line">| 男     |</div><div class="line">+--------+</div><div class="line">5 rows in set (0.00 sec)</div><div class="line"></div><div class="line"># 插入空值</div><div class="line">INSERT INTO test7 VALUES(&apos;NULL&apos;);</div><div class="line">--&gt;SELECT * FROM test7;</div><div class="line">+--------+</div><div class="line">| sex    |</div><div class="line">+--------+</div><div class="line">| 男     |</div><div class="line">| 女     |</div><div class="line">| 保密   |</div><div class="line">| 男     |</div><div class="line">| NULL   |</div><div class="line">+--------+</div><div class="line">5 rows in set (0.00 sec)</div></pre></td></tr></table></figure>
<h4 id="测试集合类型"><a href="#测试集合类型" class="headerlink" title="测试集合类型"></a>测试集合类型</h4><figure class="highlight plain"><table><tr><td class="code"><pre><div class="line"># 测试集合类型 和 枚举不同的是 集合可以选择多个值</div><div class="line">CREATE TABLE IF NOT EXISTS test8(</div><div class="line">fav  set(&apos;A&apos;,&apos;B&apos;,&apos;C&apos;,&apos;D&apos;)</div><div class="line">);</div><div class="line"># 正常插入 从集合中选择元素</div><div class="line">INSERT INTO test8 VALUES(&apos;A,B,C&apos;);</div><div class="line">INSERT INTO test8 VALUES(&apos;D,B,A&apos;);</div><div class="line"></div><div class="line">--&gt;SELECT * FROM test8;</div><div class="line">+-------+</div><div class="line">| fav   |</div><div class="line">+-------+</div><div class="line">| A,B,C |</div><div class="line">| A,B,D |</div><div class="line">+-------+</div><div class="line">2 rows in set (0.00 sec)</div></pre></td></tr></table></figure>
<h3 id="3-4-日期时间型"><a href="#3-4-日期时间型" class="headerlink" title="3.4 日期时间型"></a>3.4 日期时间型</h3><figure class="image-bubble">
                <div class="img-lightbox">
                    <div class="overlay"></div>
                    <img src="https://raw.githubusercontent.com/cucyzrd/file/master/date_and_time.png" alt="" title="">
                </div>
                <div class="image-caption"></div>
            </figure>
<h4 id="3-4-1-TIME"><a href="#3-4-1-TIME" class="headerlink" title="3.4.1 TIME"></a>3.4.1 TIME</h4><figure class="highlight plain"><table><tr><td class="code"><pre><div class="line">Name: &apos;TIME&apos;</div><div class="line">Description:</div><div class="line">TIME[(fsp)]</div><div class="line"></div><div class="line">A time. The range is &apos;-838:59:59.000000&apos; to &apos;838:59:59.000000&apos;. MySQL</div><div class="line">displays TIME values in &apos;HH:MM:SS[.fraction]&apos; format, but permits</div><div class="line">assignment of values to TIME columns using either strings or numbers.</div><div class="line"></div><div class="line">As of MySQL 5.6.4, an optional fsp value in the range from 0 to 6 may</div><div class="line">be given to specify fractional seconds precision. A value of 0</div><div class="line">signifies that there is no fractional part. If omitted, the default</div><div class="line">precision is 0.</div><div class="line"># 3个字符</div></pre></td></tr></table></figure>
<h4 id="3-4-2-DATTE"><a href="#3-4-2-DATTE" class="headerlink" title="3.4.2 DATTE"></a>3.4.2 DATTE</h4><figure class="highlight plain"><table><tr><td class="code"><pre><div class="line">Name: &apos;DATE&apos;</div><div class="line">Description:</div><div class="line">DATE</div><div class="line"></div><div class="line">A date. The supported range is &apos;1000-01-01&apos; to &apos;9999-12-31&apos;. MySQL</div><div class="line">displays DATE values in &apos;YYYY-MM-DD&apos; format, but permits assignment of</div><div class="line">values to DATE columns using either strings or numbers.</div><div class="line"># 3个字节</div></pre></td></tr></table></figure>
<h4 id="3-4-3-DATETIME"><a href="#3-4-3-DATETIME" class="headerlink" title="3.4.3 DATETIME"></a>3.4.3 DATETIME</h4><figure class="highlight plain"><table><tr><td class="code"><pre><div class="line">Name: &apos;DATETIME&apos;</div><div class="line">Description:</div><div class="line">DATETIME[(fsp)]</div><div class="line"></div><div class="line">A date and time combination. The supported range is &apos;1000-01-01</div><div class="line">00:00:00.000000&apos; to &apos;9999-12-31 23:59:59.999999&apos;. MySQL displays</div><div class="line">DATETIME values in &apos;YYYY-MM-DD HH:MM:SS[.fraction]&apos; format, but permits</div><div class="line">assignment of values to DATETIME columns using either strings or</div><div class="line">numbers.</div><div class="line"></div><div class="line">As of MySQL 5.6.4, an optional fsp value in the range from 0 to 6 may</div><div class="line">be given to specify fractional seconds precision. A value of 0</div><div class="line">signifies that there is no fractional part. If omitted, the default</div><div class="line">precision is 0.</div><div class="line"># 8个字节</div></pre></td></tr></table></figure>
<h4 id="3-4-4-TIMESTAMP"><a href="#3-4-4-TIMESTAMP" class="headerlink" title="3.4.4 TIMESTAMP"></a>3.4.4 TIMESTAMP</h4><figure class="highlight plain"><table><tr><td class="code"><pre><div class="line">Name: &apos;TIMESTAMP&apos;</div><div class="line">Description:</div><div class="line">TIMESTAMP[(fsp)]</div><div class="line"></div><div class="line">A timestamp. The range is &apos;1970-01-01 00:00:01.000000&apos; UTC to</div><div class="line">&apos;2038-01-19 03:14:07.999999&apos; UTC. TIMESTAMP values are stored as the</div><div class="line">number of seconds since the epoch (&apos;1970-01-01 00:00:00&apos; UTC). A</div><div class="line">TIMESTAMP cannot represent the value &apos;1970-01-01 00:00:00&apos; because that</div><div class="line">is equivalent to 0 seconds from the epoch and the value 0 is reserved</div><div class="line">for representing &apos;0000-00-00 00:00:00&apos;, the &quot;zero&quot; TIMESTAMP value.</div><div class="line"># 时间戳 4个字节</div></pre></td></tr></table></figure>
<h4 id="3-4-5-YEAR"><a href="#3-4-5-YEAR" class="headerlink" title="3.4.5 YEAR"></a>3.4.5 YEAR</h4><figure class="highlight plain"><table><tr><td class="code"><pre><div class="line">Name: &apos;YEAR&apos;</div><div class="line">Description:</div><div class="line">Syntax:</div><div class="line">YEAR(date)</div><div class="line"></div><div class="line">Returns the year for date, in the range 1000 to 9999, or 0 for the</div><div class="line">&quot;zero&quot; date.</div><div class="line"># 1 个字节</div></pre></td></tr></table></figure>
<h4 id="测试时间类型"><a href="#测试时间类型" class="headerlink" title="测试时间类型"></a>测试时间类型</h4><figure class="highlight plain"><table><tr><td class="code"><pre><div class="line">-- 测试YEAR类型</div><div class="line">CREATE TABLE IF NOT EXISTS `test9`(</div><div class="line">`birth` YEAR</div><div class="line">);</div><div class="line"></div><div class="line">INSERT INTO test9 VALUES(1901);</div><div class="line"></div><div class="line">SELECT * FROM test9;</div><div class="line">+-------+</div><div class="line">| birth |</div><div class="line">+-------+</div><div class="line">|  1901 |</div><div class="line">+-------+</div><div class="line">1 row in set (0.00 sec)</div><div class="line"></div><div class="line"></div><div class="line">-- 插入一个超出范围的例子</div><div class="line">INSERT INTO test9 VALUES(2156);</div><div class="line">--&gt;INSERT INTO test9 VALUES(2156);</div><div class="line">Query OK, 1 row affected, 1 warning (0.02 sec)</div><div class="line">--&gt;SHOW WARNINGS ;</div><div class="line">+---------+------+------------------------------------------------+</div><div class="line">| Level   | Code | Message                                        |</div><div class="line">+---------+------+------------------------------------------------+</div><div class="line">| Warning | 1264 | Out of range value for column &apos;birth&apos; at row 1 |</div><div class="line">+---------+------+------------------------------------------------+</div><div class="line"># 当插入YEAR的值为0时 ， 它会转为0000</div><div class="line">INSERT INTO test9 VALUES(0);</div><div class="line">--&gt;SELECT * FROM test9;</div><div class="line">+-------+</div><div class="line">| birth |</div><div class="line">+-------+</div><div class="line">|  1901 |</div><div class="line">|  0000 |</div><div class="line">+-------+</div><div class="line">2 rows in set (0.01 sec)</div></pre></td></tr></table></figure>
<figure class="highlight plain"><table><tr><td class="code"><pre><div class="line">-- TIME类型</div><div class="line">CREATE TABLE  IF NOT EXISTS test10 (</div><div class="line">test TIME</div><div class="line">);</div><div class="line"></div><div class="line">-- 插入1 天 12小时12分12秒</div><div class="line">INSERT INTO test10 VALUES(&apos;1 12:12:12&apos;); </div><div class="line">SELECT * FROM test10; </div><div class="line">SELECT * FROM test10; </div><div class="line">+----------+</div><div class="line">| test     |</div><div class="line">+----------+</div><div class="line">| 36:12:12 |</div><div class="line">+----------+</div><div class="line">1 row in set (0.00 sec)</div><div class="line"></div><div class="line">-- 11:11:00</div><div class="line">INSERT INTO test10 VALUES(&apos;11:11&apos;);</div><div class="line">SELECT * FROM test10; </div><div class="line">SELECT * FROM test10; </div><div class="line">+----------+</div><div class="line">| test     |</div><div class="line">+----------+</div><div class="line">| 36:12:12 |</div><div class="line">| 11:11:00 |</div><div class="line">+----------+</div><div class="line">2 rows in set (0.00 sec)</div><div class="line"></div><div class="line">-- 12分34秒</div><div class="line">INSERT INTO test10 VALUES(&apos;1234&apos;);</div><div class="line">SELECT * FROM test10;</div><div class="line">SELECT * FROM test10;</div><div class="line">+----------+</div><div class="line">| test     |</div><div class="line">+----------+</div><div class="line">| 36:12:12 |</div><div class="line">| 11:11:00 |</div><div class="line">| 00:12:34 |</div><div class="line">+----------+</div><div class="line">3 rows in set (0.00 sec)</div><div class="line"></div><div class="line">-- 插入12秒</div><div class="line">INSERT INTO test10 VALUES(&apos;12&apos;);</div><div class="line"></div><div class="line">-- 00:00:00</div><div class="line">INSERT INTO test10 VALUES(&apos;0&apos;);</div></pre></td></tr></table></figure>
<h3 id="3-5-BINARY"><a href="#3-5-BINARY" class="headerlink" title="3.5 BINARY"></a>3.5 BINARY</h3><figure class="highlight plain"><table><tr><td class="code"><pre><div class="line">Name: &apos;BINARY&apos;</div><div class="line">Description:</div><div class="line">BINARY(M)</div><div class="line"></div><div class="line">The BINARY type is similar to the CHAR type, but stores binary byte</div><div class="line">strings rather than nonbinary character strings. M represents the</div><div class="line">column length in bytes.</div></pre></td></tr></table></figure>
<h3 id="4-存储引擎"><a href="#4-存储引擎" class="headerlink" title="4. 存储引擎"></a>4. 存储引擎</h3><h4 id="4-1-查看支持的存储引擎"><a href="#4-1-查看支持的存储引擎" class="headerlink" title="4.1 查看支持的存储引擎"></a>4.1 查看支持的存储引擎</h4><figure class="highlight plain"><table><tr><td class="code"><pre><div class="line">SHOW ENGINES\G</div><div class="line">***************************[ 1. row ]***************************</div><div class="line">Engine       | PERFORMANCE_SCHEMA</div><div class="line">Support      | YES</div><div class="line">Comment      | Performance Schema</div><div class="line">Transactions | NO</div><div class="line">XA           | NO</div><div class="line">Savepoints   | NO</div><div class="line">***************************[ 2. row ]***************************</div><div class="line">Engine       | MRG_MYISAM</div><div class="line">Support      | YES</div><div class="line">Comment      | Collection of identical MyISAM tables</div><div class="line">Transactions | NO</div><div class="line">XA           | NO</div><div class="line">Savepoints   | NO</div><div class="line">***************************[ 3. row ]***************************</div><div class="line">Engine       | CSV</div><div class="line">Support      | YES</div><div class="line">Comment      | CSV storage engine</div><div class="line">Transactions | NO</div><div class="line">XA           | NO</div><div class="line">Savepoints   | NO</div><div class="line">***************************[ 4. row ]***************************</div><div class="line">Engine       | BLACKHOLE</div><div class="line">Support      | YES</div><div class="line">Comment      | /dev/null storage engine (anything you write to it disappears)</div><div class="line">Transactions | NO</div><div class="line">XA           | NO</div><div class="line">Savepoints   | NO</div><div class="line">***************************[ 5. row ]***************************</div><div class="line">Engine       | MEMORY</div><div class="line">Support      | YES</div><div class="line">Comment      | Hash based, stored in memory, useful for temporary tables</div><div class="line">Transactions | NO</div><div class="line">XA           | NO</div><div class="line">Savepoints   | NO</div><div class="line">***************************[ 6. row ]***************************</div><div class="line">Engine       | MyISAM</div><div class="line">Support      | YES</div><div class="line">Comment      | MyISAM storage engine</div><div class="line">Transactions | NO</div><div class="line">XA           | NO</div><div class="line">Savepoints   | NO</div><div class="line">***************************[ 7. row ]***************************</div><div class="line">Engine       | ARCHIVE</div><div class="line">Support      | YES</div><div class="line">Comment      | Archive storage engine</div><div class="line">Transactions | NO</div><div class="line">XA           | NO</div><div class="line">Savepoints   | NO</div><div class="line">***************************[ 8. row ]***************************</div><div class="line">Engine       | InnoDB</div><div class="line">Support      | DEFAULT</div><div class="line">Comment      | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys</div><div class="line">Transactions | YES 	# 支持事务</div><div class="line">XA           | YES 	# 支持分布式处理XA规范</div><div class="line">Savepoints   | YES	# 保存点</div><div class="line">***************************[ 9. row ]***************************</div><div class="line">Engine       | FEDERATED</div><div class="line">Support      | NO</div><div class="line">Comment      | Federated MySQL storage engine</div><div class="line">Transactions | None</div><div class="line">XA           | None</div><div class="line">Savepoints   | None</div></pre></td></tr></table></figure>
<p>查看当前使用的存储引擎</p>
<figure class="highlight plain"><table><tr><td class="code"><pre><div class="line">SHOW VARIABLES LIKE &apos;STORAGE_ENGINE&apos;;</div><div class="line">+-----------------+---------+</div><div class="line">| Variable_name   | Value   |</div><div class="line">|-----------------+---------|</div><div class="line">| storage_engine  | InnoDB  |</div><div class="line">+-----------------+---------+</div><div class="line">1 row in set</div></pre></td></tr></table></figure>
<h2 id="4-表的操作"><a href="#4-表的操作" class="headerlink" title="4  表的操作"></a>4  表的操作</h2><h3 id="4-1-创建表"><a href="#4-1-创建表" class="headerlink" title="4.1 创建表"></a>4.1 创建表</h3><h5 id="语法"><a href="#语法" class="headerlink" title="语法"></a>语法</h5><figure class="highlight plain"><table><tr><td class="code"><pre><div class="line">CREATE TABLE [IF NOT EXISTS] table_name(</div><div class="line">`字段名` 字段类型 [ UNSIGNED | ZEROFILL ] [ NULL | NOT NULL ] [DEFAULT &apos;默认值&apos;] [ [PRIMARY] KEY | UNIQUE [KEY]] [AUTO_INCREMENT]  </div><div class="line">)ENGINE=InnoDB CHARSET=UTF8 AUTO_INCREMENT=100;</div></pre></td></tr></table></figure>
<p><code>例子1</code></p>
<figure class="highlight plain"><table><tr><td class="code"><pre><div class="line">CREATE DATABASE IF NOT EXISTS  zrd_test DEFAULT CHARACTER SET &apos;UTF8&apos;;</div><div class="line"></div><div class="line">USE zrd_test;</div><div class="line"></div><div class="line">-- 创建学员表</div><div class="line">-- 编号id</div><div class="line">-- 用户名 username</div><div class="line">-- 年龄 age</div><div class="line">-- 性别 sex</div><div class="line">-- 邮箱 email</div><div class="line">-- 地址 addr</div><div class="line">-- 生日 birth</div><div class="line">-- 薪水 salary</div><div class="line">-- 电话 tel</div><div class="line">-- 婚否 married</div><div class="line">-- 注意：当需要输入中文的时候，需要临时转换客户端的编码方式</div><div class="line">-- SET NAMES GBK; 当前会话有效.</div><div class="line">-- COMMENT 给字段添加注释</div><div class="line"></div><div class="line">CREATE TABLE `user` (</div><div class="line">`id` SMALLINT(5) UNSIGNED NOT NULL,</div><div class="line">`username` VARCHAR(20) NOT NULL,</div><div class="line">`age` TINYINT(200) UNSIGNED NOT NULL,</div><div class="line">`sex` ENUM(&apos;男&apos;,&apos;女&apos;,&apos;保密&apos;) NOT NULL,</div><div class="line">`email` VARCHAR(50) NOT NULL,</div><div class="line">`addr` VARCHAR(200) NOT NULL,</div><div class="line">`salary` FLOAT(8,2) NOT NULL,</div><div class="line">`tel` INT(11) NOT NULL,</div><div class="line">`married` TINYINT(1) NOT NULL COMMENT &apos;0表示已婚,非0表示未婚&apos;</div><div class="line">)</div><div class="line">COMMENT = &apos;创建一张学员表信息&apos;</div><div class="line">CHARSET = &apos;utf8&apos;</div><div class="line">ENGINE 	= InnoDB</div><div class="line">;</div></pre></td></tr></table></figure>
<p><code>例子2</code></p>
<figure class="highlight plain"><table><tr><td class="code"><pre><div class="line">-- 创建课程表 course</div><div class="line">-- 编号 cid</div><div class="line">-- 课程名称 courseName</div><div class="line">-- 课程描述 courseDesc</div><div class="line">CREATE TABLE IF NOT EXISTS course (</div><div class="line">cid TINYINT,</div><div class="line">courseName VARCHAR(50),</div><div class="line">courseDesc VARCHAR(200)</div><div class="line">);</div></pre></td></tr></table></figure>
<h5 id="小练习"><a href="#小练习" class="headerlink" title="小练习"></a>小练习</h5><figure class="highlight plain"><table><tr><td class="code"><pre><div class="line">小练习</div><div class="line">1.</div><div class="line">-- 创建新闻分类表 cms_cate</div><div class="line">-- 编号、分类名称、分类描述</div><div class="line"></div><div class="line">2. </div><div class="line">-- 创建新闻表 cms_news</div><div class="line">-- 编号、新闻标题、新闻内容、新闻发布时间、点击量、是否置顶、新闻所属分类、发布人</div></pre></td></tr></table></figure>
<figure class="highlight plain"><table><tr><td class="code"><pre><div class="line"># 1</div><div class="line">CREATE TABLE IF NOT EXISTS `use_cate` ( `id` TINYINT, </div><div class="line">	`cateName` VARCHAR(50), </div><div class="line">	`cateDesc` VARCHAR(200) </div><div class="line">) ENGINE = MYISAM CHARSET= UTF8;</div><div class="line"># 2</div><div class="line">CREATE TABLE IF NOT EXISTS `cms_news` (</div><div class="line">`id` INT,</div><div class="line">`title` VARCHAR(50),</div><div class="line">`content` TEXT,</div><div class="line">`pubTime` INT,</div><div class="line">`clickNum` INT,</div><div class="line">`isTop` TINYINT(1) COMMENT &apos;0 is not top, 1 is top&apos;,</div><div class="line">`cID` TINYINT,</div><div class="line">`pubAuth` VARCHAR(50)</div><div class="line">);</div></pre></td></tr></table></figure>
<h3 id="4-2-查看表"><a href="#4-2-查看表" class="headerlink" title="4.2 查看表"></a>4.2 查看表</h3><figure class="highlight plain"><table><tr><td class="code"><pre><div class="line">percona root@localhost:test&gt; SHOW TABLES;</div><div class="line">+------------------+</div><div class="line">| Tables_in_test   |</div><div class="line">|------------------|</div><div class="line">| course           |</div><div class="line">| user             |</div><div class="line">+------------------+</div><div class="line">2 rows in set</div><div class="line">Time: 0.001s</div></pre></td></tr></table></figure>
<p>到数据目录下可以看到两个文件相对于`INNODB来说</p>
<figure class="highlight plain"><table><tr><td class="code"><pre><div class="line"># innodb存储引擎  </div><div class="line">~sehll#ls</div><div class="line">user.frm  	# 表结构</div><div class="line">user.ibd	# 表数据</div><div class="line"></div><div class="line"># myisam存储引擎</div><div class="line">└─&gt; ls</div><div class="line">use_cate.frm  	# 表结构</div><div class="line">use_cate.MYD	# 表数据  </div><div class="line">use_cate.MYI	# 索引</div></pre></td></tr></table></figure>
<p>4.1.1 创建表测试整型</p>
<figure class="highlight plain"><table><tr><td class="code"><pre><div class="line"></div><div class="line"></div></pre></td></tr></table></figure>
<h4 id="4-2-1-查看表结构"><a href="#4-2-1-查看表结构" class="headerlink" title="4.2.1 查看表结构"></a>4.2.1 查看表结构</h4><figure class="highlight plain"><table><tr><td class="code"><pre><div class="line"># 方法1</div><div class="line">--&gt;DESC cms_news;</div><div class="line">+----------+-------------+------+-----+---------+-------+</div><div class="line">| Field    | Type        | Null | Key | Default | Extra |</div><div class="line">+----------+-------------+------+-----+---------+-------+</div><div class="line">| id       | int(11)     | YES  |     | NULL    |       |</div><div class="line">| title    | varchar(50) | YES  |     | NULL    |       |</div><div class="line">| content  | text        | YES  |     | NULL    |       |</div><div class="line">| pubTime  | int(11)     | YES  |     | NULL    |       |</div><div class="line">| clickNum | int(11)     | YES  |     | NULL    |       |</div><div class="line">| isTop    | tinyint(1)  | YES  |     | NULL    |       |</div><div class="line">| cID      | tinyint(4)  | YES  |     | NULL    |       |</div><div class="line">| pubAuth  | varchar(50) | YES  |     | NULL    |       |</div><div class="line">+----------+-------------+------+-----+---------+-------+</div><div class="line">8 rows in set (0.00 sec)</div><div class="line"></div><div class="line"># 方法2</div><div class="line">--&gt;DESCRIBE cms_news;</div><div class="line">+----------+-------------+------+-----+---------+-------+</div><div class="line">| Field    | Type        | Null | Key | Default | Extra |</div><div class="line">+----------+-------------+------+-----+---------+-------+</div><div class="line">| id       | int(11)     | YES  |     | NULL    |       |</div><div class="line">| title    | varchar(50) | YES  |     | NULL    |       |</div><div class="line">| content  | text        | YES  |     | NULL    |       |</div><div class="line">| pubTime  | int(11)     | YES  |     | NULL    |       |</div><div class="line">| clickNum | int(11)     | YES  |     | NULL    |       |</div><div class="line">| isTop    | tinyint(1)  | YES  |     | NULL    |       |</div><div class="line">| cID      | tinyint(4)  | YES  |     | NULL    |       |</div><div class="line">| pubAuth  | varchar(50) | YES  |     | NULL    |       |</div><div class="line">+----------+-------------+------+-----+---------+-------+</div><div class="line">8 rows in set (0.00 sec)</div><div class="line"># 方法3</div><div class="line">--&gt;SHOW COLUMNS FROM cms_news;</div><div class="line">+----------+-------------+------+-----+---------+-------+</div><div class="line">| Field    | Type        | Null | Key | Default | Extra |</div><div class="line">+----------+-------------+------+-----+---------+-------+</div><div class="line">| id       | int(11)     | YES  |     | NULL    |       |</div><div class="line">| title    | varchar(50) | YES  |     | NULL    |       |</div><div class="line">| content  | text        | YES  |     | NULL    |       |</div><div class="line">| pubTime  | int(11)     | YES  |     | NULL    |       |</div><div class="line">| clickNum | int(11)     | YES  |     | NULL    |       |</div><div class="line">| isTop    | tinyint(1)  | YES  |     | NULL    |       |</div><div class="line">| cID      | tinyint(4)  | YES  |     | NULL    |       |</div><div class="line">| pubAuth  | varchar(50) | YES  |     | NULL    |       |</div><div class="line">+----------+-------------+------+-----+---------+-------+</div><div class="line">8 rows in set (0.00 sec)</div><div class="line"></div><div class="line"># 第一段		--&gt; 字段名</div><div class="line"># 第二段		--&gt; 字段类型</div><div class="line"># NULL		  --&gt; 是否为空</div><div class="line"># key   	  --&gt; 主键唯一</div><div class="line"># default 	  --&gt; 默认值</div><div class="line"># extra	  	  --&gt; 额外信息</div></pre></td></tr></table></figure>
<h3 id="4-3-INSERT"><a href="#4-3-INSERT" class="headerlink" title="4.3 INSERT"></a>4.3 INSERT</h3><p><code>语法</code></p>
<figure class="highlight plain"><table><tr><td class="code"><pre><div class="line">Name: &apos;INSERT&apos;</div><div class="line">Description:</div><div class="line">Syntax:</div><div class="line">INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]</div><div class="line">    [INTO] tbl_name</div><div class="line">    [PARTITION (partition_name,...)] </div><div class="line">    [(col_name,...)]</div><div class="line">    &#123;VALUES | VALUE&#125; (&#123;expr | DEFAULT&#125;,...),(...),...</div><div class="line">    [ ON DUPLICATE KEY UPDATE</div><div class="line">      col_name=expr</div><div class="line">        [, col_name=expr] ... ]</div><div class="line"></div><div class="line">Or:</div><div class="line"></div><div class="line">INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]</div><div class="line">    [INTO] tbl_name</div><div class="line">    [PARTITION (partition_name,...)]</div><div class="line">    SET col_name=&#123;expr | DEFAULT&#125;, ...</div><div class="line">    [ ON DUPLICATE KEY UPDATE</div><div class="line">      col_name=expr</div><div class="line">        [, col_name=expr] ... ]</div><div class="line"></div><div class="line">Or:</div><div class="line"></div><div class="line">INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]</div><div class="line">    [INTO] tbl_name</div><div class="line">    [PARTITION (partition_name,...)] </div><div class="line">    [(col_name,...)]</div><div class="line">    SELECT ...</div><div class="line">    [ ON DUPLICATE KEY UPDATE</div><div class="line">      col_name=expr</div><div class="line">        [, col_name=expr] ... ]</div></pre></td></tr></table></figure>
<h3 id="4-4-表结构修改"><a href="#4-4-表结构修改" class="headerlink" title="4.4  表结构修改"></a>4.4  表结构修改</h3><h4 id="4-4-1-重命名表"><a href="#4-4-1-重命名表" class="headerlink" title="4.4.1 重命名表"></a>4.4.1 重命名表</h4><figure class="highlight plain"><table><tr><td class="code"><pre><div class="line"># 创建一张用户表</div><div class="line">CREATE TABLE IF NOT EXISTS user10 (</div><div class="line">id SMALLINT UNSIGNED KEY AUTO_INCREMENT,</div><div class="line">username VARCHAR(20) NOT NULL UNIQUE,</div><div class="line">password CHAR(32) NOT NULL,</div><div class="line">email varchar(50) NOT NULL DEFAULT &apos;1234@163.com&apos;,</div><div class="line">age TINYINT UNSIGNED DEFAULT 18,</div><div class="line">addr VARCHAR(200) NOT NULL DEFAULT &apos;北京&apos;,</div><div class="line">salary FLOAT(6,2),</div><div class="line">regTime INT UNSIGNED ,</div><div class="line">face CHAR(100) NOT NULL DEFAULT &apos;default.jpg&apos;,</div><div class="line">sex ENUM(&apos;男&apos;,&apos;女&apos;,&apos;保密&apos;) DEFAULT &apos;保密&apos;</div><div class="line"></div><div class="line">) ;</div><div class="line"></div><div class="line">DESC  user10;</div><div class="line">+----------+----------------------------+------+-----+--------------+----------------+</div><div class="line">| Field    | Type                       | Null | Key | Default      | Extra          |</div><div class="line">+----------+----------------------------+------+-----+--------------+----------------+</div><div class="line">| id       | smallint(5) unsigned       | NO   | PRI | NULL         | auto_increment |</div><div class="line">| username | varchar(20)                | NO   | UNI | NULL         |                |</div><div class="line">| password | char(32)                   | NO   |     | NULL         |                |</div><div class="line">| email    | varchar(50)                | NO   |     | 1234@163.com |                |</div><div class="line">| age      | tinyint(3) unsigned        | YES  |     | 18           |                |</div><div class="line">| addr     | varchar(200)               | NO   |     | 北京         |                |</div><div class="line">| salary   | float(6,2)                 | YES  |     | NULL         |                |</div><div class="line">| regTime  | int(10) unsigned           | YES  |     | NULL         |                |</div><div class="line">| face     | char(100)                  | NO   |     | default.jpg  |                |</div><div class="line">| sex      | enum(&apos;男&apos;,&apos;女&apos;,&apos;保密&apos;)     | YES  |     | 保密         |                |</div><div class="line">+----------+----------------------------+------+-----+--------------+----------------+</div><div class="line">10 rows in set (0.00 sec)</div><div class="line"></div><div class="line"></div><div class="line"># 方法1 TO 将user10 重命名为user11</div><div class="line">ALTER TABLE user10 RENAME TO user11; </div><div class="line"># 方法2 AS</div><div class="line">ALTER TABLE user11 RENAME AS user10;</div><div class="line"># 方法3 直接写</div><div class="line">ALTER TABLE user10 RENAME user11;</div><div class="line"># 以上 to 或者AS 可以省略</div><div class="line"></div><div class="line"></div><div class="line"># 另一种方法修改 RENAME TABLE table_name_old TO table_name_new</div><div class="line">RENAME TABLE user11 TO user10;</div><div class="line"># TO 不能省略</div></pre></td></tr></table></figure>
<h4 id="4-4-2-添加字段"><a href="#4-4-2-添加字段" class="headerlink" title="4.4.2 添加字段"></a>4.4.2 添加字段</h4><figure class="highlight plain"><table><tr><td class="code"><pre><div class="line"># 添加card 字段CHAR(18) </div><div class="line">DESC user8;</div><div class="line">+----------+----------------------------+------+-----+---------+----------------+</div><div class="line">| Field    | Type                       | Null | Key | Default | Extra          |</div><div class="line">+----------+----------------------------+------+-----+---------+----------------+</div><div class="line">| id       | int(10) unsigned           | NO   | PRI | NULL    | auto_increment |</div><div class="line">| username | varchar(20)                | NO   |     | NULL    |                |</div><div class="line">| password | char(32)                   | NO   |     | NULL    |                |</div><div class="line">| age      | tinyint(3) unsigned        | YES  |     | 18      |                |</div><div class="line">| addr     | varchar(50)                | NO   |     | beijing |                |</div><div class="line">| sex      | enum(&apos;男&apos;,&apos;女&apos;,&apos;保密&apos;)     | NO   |     | 男      |                |</div><div class="line">+----------+----------------------------+------+-----+---------+----------------+</div><div class="line">6 rows in set (0.00 sec)</div><div class="line"></div><div class="line">ALTER  TABLE `user8` ADD card CHAR(18); # 默认添加到末尾</div><div class="line">Query OK, 0 rows affected (0.18 sec)</div><div class="line">Records: 0  Duplicates: 0  Warnings: 0</div><div class="line">DESC user8;</div><div class="line">+----------+----------------------------+------+-----+---------+----------------+</div><div class="line">| Field    | Type                       | Null | Key | Default | Extra          |</div><div class="line">+----------+----------------------------+------+-----+---------+----------------+</div><div class="line">| id       | int(10) unsigned           | NO   | PRI | NULL    | auto_increment |</div><div class="line">| username | varchar(20)                | NO   |     | NULL    |                |</div><div class="line">| password | char(32)                   | NO   |     | NULL    |                |</div><div class="line">| age      | tinyint(3) unsigned        | YES  |     | 18      |                |</div><div class="line">| addr     | varchar(50)                | NO   |     | beijing |                |</div><div class="line">| sex      | enum(&apos;男&apos;,&apos;女&apos;,&apos;保密&apos;)     | NO   |     | 男      |                |</div><div class="line">| card     | char(18)                   | YES  |     | NULL    |                |</div><div class="line">+----------+----------------------------+------+-----+---------+----------------+</div><div class="line">7 rows in set (0.00 sec)</div><div class="line"></div><div class="line"># 添加完整性约束</div><div class="line"></div><div class="line"># 添加到第一行</div><div class="line">ALTER TABLE `user8` ADD COLUMN `test1` VARCHAR(100) NOT NULL FIRST;</div><div class="line"># 添加到指定的字段(username之后)</div><div class="line">ALTER TABLE user8 ADD test3 INT NOT NULL DEFAULT 100 AFTER username;</div><div class="line">DESC user8;</div><div class="line">+----------+----------------------------+------+-----+---------+-------+</div><div class="line">| Field    | Type                       | Null | Key | Default | Extra |</div><div class="line">+----------+----------------------------+------+-----+---------+-------+</div><div class="line">| test1    | varchar(100)               | NO   |     | NULL    |       |</div><div class="line">| username | varchar(20)                | NO   |     | NULL    |       |</div><div class="line">| test3    | int(11)                    | NO   |     | 100     |       |</div><div class="line">| password | char(32)                   | NO   |     | NULL    |       |</div><div class="line">| age      | tinyint(3) unsigned        | YES  |     | 18      |       |</div><div class="line">| addr     | varchar(50)                | NO   |     | beijing |       |</div><div class="line">| sex      | enum(&apos;男&apos;,&apos;女&apos;,&apos;保密&apos;)     | NO   |     | 男      |       |</div><div class="line">| card     | char(18)                   | YES  |     | NULL    |       |</div><div class="line">+----------+----------------------------+------+-----+---------+-------+</div><div class="line">8 rows in set (0.00 sec)</div><div class="line"># 选中一次表 完成多个操作</div><div class="line">ALTER TABLE user8</div><div class="line">ADD test4 INT NOT NULL DEFAULT 123 AFTER password,</div><div class="line">ADD test5 FLOAT(6,2) FIRST,</div><div class="line">ADD test6 SET(&apos;A&apos;,&apos;B&apos;,&apos;C&apos;);</div><div class="line"></div><div class="line">DESC user8;</div><div class="line">+----------+----------------------------+------+-----+---------+-------+</div><div class="line">| Field    | Type                       | Null | Key | Default | Extra |</div><div class="line">+----------+----------------------------+------+-----+---------+-------+</div><div class="line">| test5    | float(6,2)                 | YES  |     | NULL    |       |</div><div class="line">| test1    | varchar(100)               | NO   |     | NULL    |       |</div><div class="line">| username | varchar(20)                | NO   |     | NULL    |       |</div><div class="line">| test3    | int(11)                    | NO   |     | 100     |       |</div><div class="line">| password | char(32)                   | NO   |     | NULL    |       |</div><div class="line">| test4    | int(11)                    | NO   |     | 123     |       |</div><div class="line">| age      | tinyint(3) unsigned        | YES  |     | 18      |       |</div><div class="line">| addr     | varchar(50)                | NO   |     | beijing |       |</div><div class="line">| sex      | enum(&apos;男&apos;,&apos;女&apos;,&apos;保密&apos;)     | NO   |     | 男      |       |</div><div class="line">| card     | char(18)                   | YES  |     | NULL    |       |</div><div class="line">| test6    | set(&apos;A&apos;,&apos;B&apos;,&apos;C&apos;)           | YES  |     | NULL    |       |</div><div class="line">+----------+----------------------------+------+-----+---------+-------+</div><div class="line">11 rows in set (0.00 sec)</div></pre></td></tr></table></figure>
<h4 id="4-4-3-删除字段"><a href="#4-4-3-删除字段" class="headerlink" title="4.4.3 删除字段"></a>4.4.3 删除字段</h4><figure class="highlight plain"><table><tr><td class="code"><pre><div class="line"># 删除字段test6 </div><div class="line">ALTER TABLE user8  DROP test6;</div><div class="line">DESC user8;</div><div class="line">+----------+----------------------------+------+-----+---------+-------+</div><div class="line">| Field    | Type                       | Null | Key | Default | Extra |</div><div class="line">+----------+----------------------------+------+-----+---------+-------+</div><div class="line">| test5    | float(6,2)                 | YES  |     | NULL    |       |</div><div class="line">| test1    | varchar(100)               | NO   |     | NULL    |       |</div><div class="line">| username | varchar(20)                | NO   |     | NULL    |       |</div><div class="line">| test3    | int(11)                    | NO   |     | 100     |       |</div><div class="line">| password | char(32)                   | NO   |     | NULL    |       |</div><div class="line">| test4    | int(11)                    | NO   |     | 123     |       |</div><div class="line">| age      | tinyint(3) unsigned        | YES  |     | 18      |       |</div><div class="line">| addr     | varchar(50)                | NO   |     | beijing |       |</div><div class="line">| sex      | enum(&apos;男&apos;,&apos;女&apos;,&apos;保密&apos;)     | NO   |     | 男      |       |</div><div class="line">| card     | char(18)                   | YES  |     | NULL    |       |</div><div class="line">+----------+----------------------------+------+-----+---------+-------+</div><div class="line">10 rows in set (0.00 sec)</div><div class="line"></div><div class="line"># 一次选中表，删除多个字段</div><div class="line">ALTER TABLE user8</div><div class="line">DROP test3,</div><div class="line">DROP test4,</div><div class="line">DROP test5;</div><div class="line">Query OK, 0 rows affected (0.11 sec)</div><div class="line">Records: 0  Duplicates: 0  Warnings: 0</div><div class="line">DESC user8;</div><div class="line">+----------+----------------------------+------+-----+---------+-------+</div><div class="line">| Field    | Type                       | Null | Key | Default | Extra |</div><div class="line">+----------+----------------------------+------+-----+---------+-------+</div><div class="line">| test1    | varchar(100)               | NO   |     | NULL    |       |</div><div class="line">| username | varchar(20)                | NO   |     | NULL    |       |</div><div class="line">| password | char(32)                   | NO   |     | NULL    |       |</div><div class="line">| age      | tinyint(3) unsigned        | YES  |     | 18      |       |</div><div class="line">| addr     | varchar(50)                | NO   |     | beijing |       |</div><div class="line">| sex      | enum(&apos;男&apos;,&apos;女&apos;,&apos;保密&apos;)     | NO   |     | 男      |       |</div><div class="line">| card     | char(18)                   | YES  |     | NULL    |       |</div><div class="line">+----------+----------------------------+------+-----+---------+-------+</div><div class="line">7 rows in set (0.00 sec)</div><div class="line"></div><div class="line"># 添加test字段 删除card字段</div><div class="line">ALTER TABLE user8</div><div class="line">ADD test INT UNSIGNED NOT NULL DEFAULT 10 AFTER sex,</div><div class="line">DROP card;</div><div class="line">Query OK, 0 rows affected (0.11 sec)</div><div class="line">Records: 0  Duplicates: 0  Warnings: 0</div><div class="line">+----------+----------------------------+------+-----+---------+-------+</div><div class="line">| Field    | Type                       | Null | Key | Default | Extra |</div><div class="line">+----------+----------------------------+------+-----+---------+-------+</div><div class="line">| test1    | varchar(100)               | NO   |     | NULL    |       |</div><div class="line">| username | varchar(20)                | NO   |     | NULL    |       |</div><div class="line">| password | char(32)                   | NO   |     | NULL    |       |</div><div class="line">| age      | tinyint(3) unsigned        | YES  |     | 18      |       |</div><div class="line">| addr     | varchar(50)                | NO   |     | beijing |       |</div><div class="line">| sex      | enum(&apos;男&apos;,&apos;女&apos;,&apos;保密&apos;)     | NO   |     | 男      |       |</div><div class="line">| test     | int(10) unsigned           | NO   |     | 10      |       |</div><div class="line">+----------+----------------------------+------+-----+---------+-------+</div><div class="line">7 rows in set (0.00 sec)</div></pre></td></tr></table></figure>
<h4 id="4-4-4-修改字段"><a href="#4-4-4-修改字段" class="headerlink" title="4.4.4 修改字段"></a>4.4.4 修改字段</h4><figure class="highlight plain"><table><tr><td class="code"><pre><div class="line"># 修改字段</div><div class="line">ALTER TABLE tbl_name MODIFY 字段名 字段类型[完整性约束] [FIRST|AFTER]</div><div class="line"></div><div class="line"># 将Email 修改成VARCHAR(200)</div><div class="line">ALTER TABLE user10 MODIFY email VARCHAR(200) NOT NULL DEFAULT &apos;2920@qq.com&apos;;</div><div class="line">Query OK, 0 rows affected (0.12 sec)</div><div class="line">Records: 0  Duplicates: 0  Warnings: 0</div><div class="line">desc user10;</div><div class="line">+----------+----------------------------+------+-----+-------------+----------------+</div><div class="line">| Field    | Type                       | Null | Key | Default     | Extra          |</div><div class="line">+----------+----------------------------+------+-----+-------------+----------------+</div><div class="line">| id       | smallint(5) unsigned       | NO   | PRI | NULL        | auto_increment |</div><div class="line">| username | varchar(20)                | NO   | UNI | NULL        |                |</div><div class="line">| password | char(32)                   | NO   |     | NULL        |                |</div><div class="line">| email    | varchar(200)               | NO   |     | 2920@qq.com |                |</div><div class="line">| age      | tinyint(3) unsigned        | YES  |     | 18          |                |</div><div class="line">| addr     | varchar(200)               | NO   |     | 北京        |                |</div><div class="line">| salary   | float(6,2)                 | YES  |     | NULL        |                |</div><div class="line">| regTime  | int(10) unsigned           | YES  |     | NULL        |                |</div><div class="line">| face     | char(100)                  | NO   |     | default.jpg |                |</div><div class="line">| sex      | enum(&apos;男&apos;,&apos;女&apos;,&apos;保密&apos;)     | YES  |     | 保密        |                |</div><div class="line">+----------+----------------------------+------+-----+-------------+----------------+</div><div class="line">10 rows in set (0.00 sec)</div><div class="line"></div><div class="line"># 修改字段的位置 将 addr 移动到sex之后</div><div class="line">ALTER TABLE user10 MODIFY addr VARCHAR(200) NOT NULL DEFAULT &apos;北京&apos; AFTER sex;</div><div class="line">Query OK, 0 rows affected (0.08 sec)</div><div class="line">Records: 0  Duplicates: 0  Warnings: 0</div><div class="line">DESC user10;</div><div class="line">+----------+----------------------------+------+-----+-------------+----------------+</div><div class="line">| Field    | Type                       | Null | Key | Default     | Extra          |</div><div class="line">+----------+----------------------------+------+-----+-------------+----------------+</div><div class="line">| id       | smallint(5) unsigned       | NO   | PRI | NULL        | auto_increment |</div><div class="line">| username | varchar(20)                | NO   | UNI | NULL        |                |</div><div class="line">| password | char(32)                   | NO   |     | NULL        |                |</div><div class="line">| email    | varchar(200)               | NO   |     | 2920@qq.com |                |</div><div class="line">| age      | tinyint(3) unsigned        | YES  |     | 18          |                |</div><div class="line">| salary   | float(6,2)                 | YES  |     | NULL        |                |</div><div class="line">| regTime  | int(10) unsigned           | YES  |     | NULL        |                |</div><div class="line">| face     | char(100)                  | NO   |     | default.jpg |                |</div><div class="line">| sex      | enum(&apos;男&apos;,&apos;女&apos;,&apos;保密&apos;)     | YES  |     | 保密        |                |</div><div class="line">| addr     | varchar(200)               | NO   |     | 北京        |                |</div><div class="line">+----------+----------------------------+------+-----+-------------+----------------+</div><div class="line">10 rows in set (0.00 sec)</div><div class="line"></div><div class="line"># 将addr字段修改位CHAR(32) NOT NULL DEFAULT &apos;123&apos; 移动到第一个位置</div><div class="line">ALTER TABLE user10 MODIFY addr CHAR(30) NOT NULL DEFAULT &apos;123&apos; FIRST;</div><div class="line">DESC user10;</div><div class="line">+----------+----------------------------+------+-----+-------------+----------------+</div><div class="line">| Field    | Type                       | Null | Key | Default     | Extra          |</div><div class="line">+----------+----------------------------+------+-----+-------------+----------------+</div><div class="line">| addr     | char(30)                   | NO   |     | 123         |                |</div><div class="line">| id       | smallint(5) unsigned       | NO   | PRI | NULL        | auto_increment |</div><div class="line">| username | varchar(20)                | NO   | UNI | NULL        |                |</div><div class="line">| password | char(32)                   | NO   |     | NULL        |                |</div><div class="line">| email    | varchar(200)               | NO   |     | 2920@qq.com |                |</div><div class="line">| age      | tinyint(3) unsigned        | YES  |     | 18          |                |</div><div class="line">| salary   | float(6,2)                 | YES  |     | NULL        |                |</div><div class="line">| regTime  | int(10) unsigned           | YES  |     | NULL        |                |</div><div class="line">| face     | char(100)                  | NO   |     | default.jpg |                |</div><div class="line">| sex      | enum(&apos;男&apos;,&apos;女&apos;,&apos;保密&apos;)     | YES  |     | 保密        |                |</div><div class="line">+----------+----------------------------+------+-----+-------------+----------------+</div><div class="line">10 rows in set (0.00 sec)</div><div class="line"></div><div class="line"></div><div class="line"></div><div class="line"># 字段名修改</div><div class="line">ALTER TABLE tbl_name CHANGE 旧字段名称 新字段名称 字段类型 [完整性约束条件] [FIRST|AFTER 字段名称]</div><div class="line"># 将test字段改为test1</div><div class="line">ALTER TABLE user10 CHANGE test test1 CHAR(32) NOT NULL DEFAULT &apos;123&apos;;</div><div class="line">DESC user10;</div><div class="line">+----------+----------------------------+------+-----+-------------+----------------+</div><div class="line">| Field    | Type                       | Null | Key | Default     | Extra          |</div><div class="line">+----------+----------------------------+------+-----+-------------+----------------+</div><div class="line">| addr     | char(30)                   | NO   |     | 123         |                |</div><div class="line">| id       | smallint(5) unsigned       | NO   | PRI | NULL        | auto_increment |</div><div class="line">| username | varchar(20)                | NO   | UNI | NULL        |                |</div><div class="line">| password | char(32)                   | NO   |     | NULL        |                |</div><div class="line">| email    | varchar(200)               | NO   |     | 2920@qq.com |                |</div><div class="line">| age      | tinyint(3) unsigned        | YES  |     | 18          |                |</div><div class="line">| salary   | float(6,2)                 | YES  |     | NULL        |                |</div><div class="line">| regTime  | int(10) unsigned           | YES  |     | NULL        |                |</div><div class="line">| face     | char(100)                  | NO   |     | default.jpg |                |</div><div class="line">| sex      | enum(&apos;男&apos;,&apos;女&apos;,&apos;保密&apos;)     | YES  |     | 保密        |                |</div><div class="line">| test1    | char(32)                   | NO   |     | 123         |                |</div><div class="line">+----------+----------------------------+------+-----+-------------+----------------+</div><div class="line">11 rows in set (0.00 sec)</div><div class="line"></div><div class="line"># 修改字段属性，但是不修改字段名</div><div class="line">ALTER TABLE user10 CHANGE test1 test1 INT; </div><div class="line">DESC user10;</div><div class="line">+----------+----------------------------+------+-----+-------------+----------------+</div><div class="line">| Field    | Type                       | Null | Key | Default     | Extra          |</div><div class="line">+----------+----------------------------+------+-----+-------------+----------------+</div><div class="line">| addr     | char(30)                   | NO   |     | 123         |                |</div><div class="line">| id       | smallint(5) unsigned       | NO   | PRI | NULL        | auto_increment |</div><div class="line">| username | varchar(20)                | NO   | UNI | NULL        |                |</div><div class="line">| password | char(32)                   | NO   |     | NULL        |                |</div><div class="line">| email    | varchar(200)               | NO   |     | 2920@qq.com |                |</div><div class="line">| age      | tinyint(3) unsigned        | YES  |     | 18          |                |</div><div class="line">| salary   | float(6,2)                 | YES  |     | NULL        |                |</div><div class="line">| regTime  | int(10) unsigned           | YES  |     | NULL        |                |</div><div class="line">| face     | char(100)                  | NO   |     | default.jpg |                |</div><div class="line">| sex      | enum(&apos;男&apos;,&apos;女&apos;,&apos;保密&apos;)     | YES  |     | 保密        |                |</div><div class="line">| test1    | int(11)                    | YES  |     | NULL        |                |</div><div class="line">+----------+----------------------------+------+-----+-------------+----------------+</div><div class="line">11 rows in set (0.00 sec)</div></pre></td></tr></table></figure>
<h4 id="4-4-5-添加删除默认值"><a href="#4-4-5-添加删除默认值" class="headerlink" title="4.4.5 添加删除默认值"></a>4.4.5 添加删除默认值</h4><figure class="highlight plain"><table><tr><td class="code"><pre><div class="line"># 添加默认值</div><div class="line">ALTER TABLE tbl_name ALTER 字段名称 SET DEFAULT 默认值</div><div class="line"></div><div class="line">CREATE TABLE IF NOT EXISTS user11 (</div><div class="line">id TINYINT UNSIGNED KEY AUTO_INCREMENT,</div><div class="line">username VARCHAR(20) NOT NULL UNIQUE,</div><div class="line">age TINYINT UNSIGNED</div><div class="line">);</div><div class="line"></div><div class="line">DESC user11;</div><div class="line">+----------+---------------------+------+-----+---------+----------------+</div><div class="line">| Field    | Type                | Null | Key | Default | Extra          |</div><div class="line">+----------+---------------------+------+-----+---------+----------------+</div><div class="line">| id       | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |</div><div class="line">| username | varchar(20)         | NO   | UNI | NULL    |                |</div><div class="line">| age      | tinyint(3) unsigned | YES  |     | NULL    |                |</div><div class="line">+----------+---------------------+------+-----+---------+----------------+</div><div class="line">3 rows in set (0.00 sec)</div><div class="line"></div><div class="line">ALTER TABLE user11 ALTER age SET DEFAULT 18;</div><div class="line"></div><div class="line">DESC user11;</div><div class="line">+----------+---------------------+------+-----+---------+----------------+</div><div class="line">| Field    | Type                | Null | Key | Default | Extra          |</div><div class="line">+----------+---------------------+------+-----+---------+----------------+</div><div class="line">| id       | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |</div><div class="line">| username | varchar(20)         | NO   | UNI | NULL    |                |</div><div class="line">| age      | tinyint(3) unsigned | YES  |     | 18      |                |</div><div class="line">+----------+---------------------+------+-----+---------+----------------+</div><div class="line">3 rows in set (0.01 sec)</div><div class="line"># 添加email默认值</div><div class="line">ALTER TABLE user11 ADD email VARCHAR(50) ;</div><div class="line">ALTER TABLE user11 ALTER email SET DEFAULT &apos;2920@qq.com&apos;;</div><div class="line">DESC user11;</div><div class="line">+----------+---------------------+------+-----+-------------+----------------+</div><div class="line">| Field    | Type                | Null | Key | Default     | Extra          |</div><div class="line">+----------+---------------------+------+-----+-------------+----------------+</div><div class="line">| id       | tinyint(3) unsigned | NO   | PRI | NULL        | auto_increment |</div><div class="line">| username | varchar(20)         | NO   | UNI | NULL        |                |</div><div class="line">| age      | tinyint(3) unsigned | YES  |     | 18          |                |</div><div class="line">| email    | varchar(50)         | YES  |     | 2920@qq.com |                |</div><div class="line">+----------+---------------------+------+-----+-------------+----------------+</div><div class="line">4 rows in set (0.00 sec)</div><div class="line"></div><div class="line"># 删除默认值</div><div class="line">ALTER TABLE tbl_name ALTER 字段名称 DROP DEFAULT</div><div class="line"></div><div class="line"># 删除age默认值</div><div class="line">ALTER TABLE user11 ALTER age DROP DEFAULT;</div><div class="line">DESC user11;</div><div class="line">+----------+---------------------+------+-----+-------------+----------------+</div><div class="line">| Field    | Type                | Null | Key | Default     | Extra          |</div><div class="line">+----------+---------------------+------+-----+-------------+----------------+</div><div class="line">| id       | tinyint(3) unsigned | NO   | PRI | NULL        | auto_increment |</div><div class="line">| username | varchar(20)         | NO   | UNI | NULL        |                |</div><div class="line">| age      | tinyint(3) unsigned | YES  |     | NULL        |                |</div><div class="line">| email    | varchar(50)         | YES  |     | 2920@qq.com |                |</div><div class="line">+----------+---------------------+------+-----+-------------+----------------+</div><div class="line">4 rows in set (0.00 sec)</div><div class="line"># 删除email 默认值</div><div class="line">ALTER TABLE user11 ALTER email DROP DEFAULT;</div><div class="line">DESC user11;</div><div class="line">+----------+---------------------+------+-----+---------+----------------+</div><div class="line">| Field    | Type                | Null | Key | Default | Extra          |</div><div class="line">+----------+---------------------+------+-----+---------+----------------+</div><div class="line">| id       | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |</div><div class="line">| username | varchar(20)         | NO   | UNI | NULL    |                |</div><div class="line">| age      | tinyint(3) unsigned | YES  |     | NULL    |                |</div><div class="line">| email    | varchar(50)         | YES  |     | NULL    |                |</div><div class="line">+----------+---------------------+------+-----+---------+----------------+</div><div class="line">4 rows in set (0.00 sec)</div></pre></td></tr></table></figure>
<h4 id="4-4-6-添加删除主键"><a href="#4-4-6-添加删除主键" class="headerlink" title="4.4.6 添加删除主键"></a>4.4.6 添加删除主键</h4><figure class="highlight plain"><table><tr><td class="code"><pre><div class="line"># 添加主键</div><div class="line">CREATE TABLE IF NOT EXISTS test12(</div><div class="line">id INT</div><div class="line">);</div><div class="line">DESC  test12;</div><div class="line">+-------+---------+------+-----+---------+-------+</div><div class="line">| Field | Type    | Null | Key | Default | Extra |</div><div class="line">+-------+---------+------+-----+---------+-------+</div><div class="line">| id    | int(11) | YES  |     | NULL    |       |</div><div class="line">+-------+---------+------+-----+---------+-------+</div><div class="line">1 row in set (0.00 sec)</div><div class="line"># 简单写法</div><div class="line">ALTER TABLE test12 ADD PRIMARY KEY(id);</div><div class="line">DESC  test12;</div><div class="line">+-------+---------+------+-----+---------+-------+</div><div class="line">| Field | Type    | Null | Key | Default | Extra |</div><div class="line">+-------+---------+------+-----+---------+-------+</div><div class="line">| id    | int(11) | NO   | PRI | 0       |       |</div><div class="line">+-------+---------+------+-----+---------+-------+</div><div class="line">1 row in set (0.00 sec)</div><div class="line"></div><div class="line"></div><div class="line">CREATE TABLE IF NOT EXISTS test13 (</div><div class="line">id INT,</div><div class="line">card CHAR(20),</div><div class="line">username  VARCHAR(20) NOT NULL</div><div class="line">);</div><div class="line"></div><div class="line"># 复合主键</div><div class="line">ALTER TABLE test13 ADD PRIMARY KEY (id,card);</div><div class="line"></div><div class="line"></div><div class="line"># 删除主键</div><div class="line">ALTER TABLE tbl_name DROP PRIMARY KEY</div><div class="line"># 删除tes12的主键</div><div class="line">ALTER TABLE test12 DROP PRIMARY KEY; # 因为一张表中只有一个主键</div><div class="line">DESC test12;</div><div class="line">+-------+---------+------+-----+---------+-------+</div><div class="line">| Field | Type    | Null | Key | Default | Extra |</div><div class="line">+-------+---------+------+-----+---------+-------+</div><div class="line">| id    | int(11) | NO   |     | 0       |       |</div><div class="line">+-------+---------+------+-----+---------+-------+</div><div class="line">1 row in set (0.00 sec)</div><div class="line"></div><div class="line">ALTER TABLE test13 DROP PRIMARY KEY;</div><div class="line">DESC test13;</div><div class="line">+----------+-------------+------+-----+---------+-------+</div><div class="line">| Field    | Type        | Null | Key | Default | Extra |</div><div class="line">+----------+-------------+------+-----+---------+-------+</div><div class="line">| id       | int(11)     | NO   |     | 0       |       |</div><div class="line">| card     | char(20)    | NO   |     |         |       |</div><div class="line">| username | varchar(20) | NO   |     | NULL    |       |</div><div class="line">+----------+-------------+------+-----+---------+-------+</div><div class="line">3 rows in set (0.00 sec)</div><div class="line"></div><div class="line"></div><div class="line"># 添加主键，完整形式</div><div class="line">ALTER TABLE test12 ADD CONSTRAINT symbol PRIMARY KEY index_type(id); </div><div class="line">+-------+---------+------+-----+---------+-------+</div><div class="line">| Field | Type    | Null | Key | Default | Extra |</div><div class="line">+-------+---------+------+-----+---------+-------+</div><div class="line">| id    | int(11) | NO   | PRI | 0       |       |</div><div class="line">+-------+---------+------+-----+---------+-------+</div><div class="line">1 row in set (0.00 sec)</div><div class="line"></div><div class="line"># 删除主键</div><div class="line">CREATE TABLE IF NOT EXISTS test14(</div><div class="line">id INT UNSIGNED KEY AUTO_INCREMENT</div><div class="line">);</div><div class="line">DESC test14;</div><div class="line">+-------+------------------+------+-----+---------+----------------+</div><div class="line">| Field | Type             | Null | Key | Default | Extra          |</div><div class="line">+-------+------------------+------+-----+---------+----------------+</div><div class="line">| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |</div><div class="line">+-------+------------------+------+-----+---------+----------------+</div><div class="line">1 row in set (0.00 sec)</div><div class="line"></div><div class="line">ALTER TABLE test14 DROP PRIMARY KEY;</div><div class="line">ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key</div><div class="line"># 一张表中有且一列是自增长列且要被定义成主键</div><div class="line"># 所以要先去掉自增长</div><div class="line">ALTER TABLE test14 MODIFY id INT UNSIGNED ;</div><div class="line">DESC test14;</div><div class="line">+-------+------------------+------+-----+---------+-------+</div><div class="line">| Field | Type             | Null | Key | Default | Extra |</div><div class="line">+-------+------------------+------+-----+---------+-------+</div><div class="line">| id    | int(10) unsigned | NO   | PRI | 0       |       |</div><div class="line">+-------+------------------+------+-----+---------+-------+</div><div class="line">1 row in set (0.00 sec)</div><div class="line"></div><div class="line">ALTER TABLE test14 DROP PRIMARY KEY;</div><div class="line">Query OK, 0 rows affected (0.11 sec)</div><div class="line">ESC test14;</div><div class="line">+-------+------------------+------+-----+---------+-------+</div><div class="line">| Field | Type             | Null | Key | Default | Extra |</div><div class="line">+-------+------------------+------+-----+---------+-------+</div><div class="line">| id    | int(10) unsigned | NO   |     | 0       |       |</div><div class="line">+-------+------------------+------+-----+---------+-------+</div><div class="line">1 row in set (0.01 sec)</div></pre></td></tr></table></figure>
<h4 id="4-4-7-添加删除唯一索引"><a href="#4-4-7-添加删除唯一索引" class="headerlink" title="4.4.7 添加删除唯一索引"></a>4.4.7 添加删除唯一索引</h4><figure class="highlight plain"><table><tr><td class="code"><pre><div class="line"># 测试添加唯一索引</div><div class="line">ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [索引名称](字段名称,...)</div><div class="line"></div><div class="line">CREATE TABLE IF NOT EXISTS user12 (</div><div class="line">id TINYINT UNSIGNED KEY AUTO_INCREMENT,</div><div class="line">username VARCHAR(20) NOT NULL,</div><div class="line">card CHAR(18),</div><div class="line">test VARCHAR(20) NOT NULL,</div><div class="line">test1 CHAR(32) NOT NULL</div><div class="line">);</div><div class="line"></div><div class="line">DESC user12;</div><div class="line">+----------+---------------------+------+-----+---------+----------------+</div><div class="line">| Field    | Type                | Null | Key | Default | Extra          |</div><div class="line">+----------+---------------------+------+-----+---------+----------------+</div><div class="line">| id       | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |</div><div class="line">| username | varchar(20)         | NO   |     | NULL    |                |</div><div class="line">| card     | char(18)            | YES  |     | NULL    |                |</div><div class="line">| test     | varchar(20)         | NO   |     | NULL    |                |</div><div class="line">| test1    | char(32)            | NO   |     | NULL    |                |</div><div class="line">+----------+---------------------+------+-----+---------+----------------+</div><div class="line">5 rows in set (0.00 sec)</div><div class="line"></div><div class="line"># 添加唯一性索引</div><div class="line">ALTER TABLE user12 ADD UNIQUE(username);</div><div class="line">DESC user12;</div><div class="line">+----------+---------------------+------+-----+---------+----------------+</div><div class="line">| Field    | Type                | Null | Key | Default | Extra          |</div><div class="line">+----------+---------------------+------+-----+---------+----------------+</div><div class="line">| id       | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |</div><div class="line">| username | varchar(20)         | NO   | UNI | NULL    |                |</div><div class="line">| card     | char(18)            | YES  |     | NULL    |                |</div><div class="line">| test     | varchar(20)         | NO   |     | NULL    |                |</div><div class="line">| test1    | char(32)            | NO   |     | NULL    |                |</div><div class="line">+----------+---------------------+------+-----+---------+----------------+</div><div class="line">5 rows in set (0.00 sec)</div><div class="line"># 添加唯一性索引(完整写法)</div><div class="line">ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]]  UNIQUE [INDEX|KEY] [索引名称] (字段名称,...)</div><div class="line">ALTER TABLE user12 ADD CONSTRAINT symbol UNIQUE  KEY  uni_card(card);</div><div class="line">desc user12;</div><div class="line">+----------+---------------------+------+-----+---------+----------------+</div><div class="line">| Field    | Type                | Null | Key | Default | Extra          |</div><div class="line">+----------+---------------------+------+-----+---------+----------------+</div><div class="line">| id       | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |</div><div class="line">| username | varchar(20)         | NO   | UNI | NULL    |                |</div><div class="line">| card     | char(18)            | YES  | UNI | NULL    |                |</div><div class="line">| test     | varchar(20)         | NO   |     | NULL    |                |</div><div class="line">| test1    | char(32)            | NO   |     | NULL    |                |</div><div class="line">+----------+---------------------+------+-----+---------+----------------+</div><div class="line">5 rows in set (0.00 sec)</div><div class="line"></div><div class="line">SHOW CREATE TABLE user12;</div><div class="line">CREATE TABLE `user12` (</div><div class="line">`id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,</div><div class="line">`username` varchar(20) NOT NULL,</div><div class="line">`card` char(18) DEFAULT NULL,</div><div class="line">`test` varchar(20) NOT NULL,</div><div class="line">`test1` char(32) NOT NULL,</div><div class="line">PRIMARY KEY (`id`),</div><div class="line">UNIQUE KEY `username` (`username`),</div><div class="line">UNIQUE KEY `uni_card` (`card`)</div><div class="line">) ENGINE=InnoDB DEFAULT CHARSET=utf8</div><div class="line"></div><div class="line"># 添加联合索引</div><div class="line">ALTER TABLE user12 ADD CONSTRAINT symbol UNIQUE INDEX mulUni_test_test1(test,test1);</div><div class="line">DESC user12;</div><div class="line">+----------+---------------------+------+-----+---------+----------------+</div><div class="line">| Field    | Type                | Null | Key | Default | Extra          |</div><div class="line">+----------+---------------------+------+-----+---------+----------------+</div><div class="line">| id       | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |</div><div class="line">| username | varchar(20)         | NO   | UNI | NULL    |                |</div><div class="line">| card     | char(18)            | YES  | UNI | NULL    |                |</div><div class="line">| test     | varchar(20)         | NO   | MUL | NULL    |                |</div><div class="line">| test1    | char(32)            | NO   |     | NULL    |                |</div><div class="line">+----------+---------------------+------+-----+---------+----------------+</div><div class="line">5 rows in set (0.00 sec)</div><div class="line"></div><div class="line"> CREATE TABLE `user12` (</div><div class="line">`id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,</div><div class="line">`username` varchar(20) NOT NULL,</div><div class="line">`card` char(18) DEFAULT NULL,</div><div class="line">`test` varchar(20) NOT NULL,</div><div class="line">`test1` char(32) NOT NULL,</div><div class="line">PRIMARY KEY (`id`),</div><div class="line">UNIQUE KEY `username` (`username`),</div><div class="line">UNIQUE KEY `mulUni_test_test1` (`test`,`test1`),</div><div class="line">UNIQUE KEY `uni_card` (`card`)</div><div class="line">) ENGINE=InnoDB DEFAULT CHARSET=utf8</div><div class="line"></div><div class="line"># 删除唯一索引</div><div class="line">ALTER TABLE user12  DROP INDEX username;</div><div class="line">DESC user12;</div><div class="line">+----------+---------------------+------+-----+---------+----------------+</div><div class="line">| Field    | Type                | Null | Key | Default | Extra          |</div><div class="line">+----------+---------------------+------+-----+---------+----------------+</div><div class="line">| id       | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |</div><div class="line">| username | varchar(20)         | NO   |     | NULL    |                |</div><div class="line">| card     | char(18)            | YES  | UNI | NULL    |                |</div><div class="line">| test     | varchar(20)         | NO   | MUL | NULL    |                |</div><div class="line">| test1    | char(32)            | NO   |     | NULL    |                |</div><div class="line">+----------+---------------------+------+-----+---------+----------------+</div><div class="line">5 rows in set (0.00 sec)</div><div class="line">ALTER TABLE user12 DROP KEY uni_card ;</div><div class="line">ALTER TABLE user12 DROP KEY mulUni_test_test1;</div><div class="line">DESC user12;</div><div class="line">+----------+---------------------+------+-----+---------+----------------+</div><div class="line">| Field    | Type                | Null | Key | Default | Extra          |</div><div class="line">+----------+---------------------+------+-----+---------+----------------+</div><div class="line">| id       | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |</div><div class="line">| username | varchar(20)         | NO   |     | NULL    |                |</div><div class="line">| card     | char(18)            | YES  |     | NULL    |                |</div><div class="line">| test     | varchar(20)         | NO   |     | NULL    |                |</div><div class="line">| test1    | char(32)            | NO   |     | NULL    |                |</div><div class="line">+----------+---------------------+------+-----+---------+----------------+</div><div class="line">5 rows in set (0.00 sec)</div></pre></td></tr></table></figure>
<h4 id="4-4-8-修改表存储引擎"><a href="#4-4-8-修改表存储引擎" class="headerlink" title="4.4.8 修改表存储引擎"></a>4.4.8 修改表存储引擎</h4><figure class="highlight plain"><table><tr><td class="code"><pre><div class="line"># 表存储引擎修改</div><div class="line">SHOW CREATE TABLE user12\G</div><div class="line">*************************** 1. row ***************************</div><div class="line">       Table: user12</div><div class="line">Create Table: CREATE TABLE `user12` (</div><div class="line">  `id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,</div><div class="line">  `username` varchar(20) NOT NULL,</div><div class="line">  `card` char(18) DEFAULT NULL,</div><div class="line">  `test` varchar(20) NOT NULL,</div><div class="line">  `test1` char(32) NOT NULL,</div><div class="line">  PRIMARY KEY (`id`)</div><div class="line">) ENGINE=InnoDB DEFAULT CHARSET=utf8</div><div class="line">1 row in set (0.00 sec)</div><div class="line"></div><div class="line"># 修改为 myisam 没有事务支持</div><div class="line">ALTER TABLE user12 ENGINE=MYISAM;</div><div class="line">SHOW CREATE TABLE user12\G</div><div class="line">*************************** 1. row ***************************</div><div class="line">       Table: user12</div><div class="line">Create Table: CREATE TABLE `user12` (</div><div class="line">  `id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,</div><div class="line">  `username` varchar(20) NOT NULL,</div><div class="line">  `card` char(18) DEFAULT NULL,</div><div class="line">  `test` varchar(20) NOT NULL,</div><div class="line">  `test1` char(32) NOT NULL,</div><div class="line">  PRIMARY KEY (`id`)</div><div class="line">) ENGINE=MyISAM DEFAULT CHARSET=utf8</div><div class="line"></div><div class="line"># 修改为InnoDB</div><div class="line">ALTER TABLE user12 ENGINE=InnoDB;</div></pre></td></tr></table></figure>
<h4 id="4-4-9-修改自动增长值"><a href="#4-4-9-修改自动增长值" class="headerlink" title="4.4.9 修改自动增长值"></a>4.4.9 修改自动增长值</h4><figure class="highlight plain"><table><tr><td class="code"><pre><div class="line"># 修改自增长值</div><div class="line">ALTER TABLE user12 AUTO_INCREMENT=100;</div><div class="line">SHOW CREATE TABLE user12\G</div><div class="line">*************************** 1. row ***************************</div><div class="line">       Table: user12</div><div class="line">Create Table: CREATE TABLE `user12` (</div><div class="line">  `id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,</div><div class="line">  `username` varchar(20) NOT NULL,</div><div class="line">  `card` char(18) DEFAULT NULL,</div><div class="line">  `test` varchar(20) NOT NULL,</div><div class="line">  `test1` char(32) NOT NULL,</div><div class="line">  PRIMARY KEY (`id`)</div><div class="line">) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8</div><div class="line">1 row in set (0.00 sec)</div></pre></td></tr></table></figure>
<h3 id="待整理-完整性约束"><a href="#待整理-完整性约束" class="headerlink" title="待整理-完整性约束"></a>待整理-完整性约束</h3><h4 id="主键约束-PRIMARY-KEY"><a href="#主键约束-PRIMARY-KEY" class="headerlink" title="主键约束 PRIMARY KEY"></a>主键约束 PRIMARY KEY</h4><p>唯一标识符，来找到此记录,一般主键会加到无意义的字段上，例如编号字段</p>
<p>要求:不能出现重复的值，被要求设置成主键的字段自动带有非空约束</p>
<p>mysql中可以设置为单字段，也可以多字段</p>
<figure class="highlight plain"><table><tr><td class="code"><pre><div class="line">-- 主键约束测试</div><div class="line">CREATE TABLE IF NOT EXISTS user1(</div><div class="line">id INT PRIMARY KEY,</div><div class="line">name VARCHAR(20)</div><div class="line">);</div><div class="line">Query OK, 0 rows affected (0.06 sec)</div><div class="line"></div><div class="line">DESC user1; # 可以看到KEY 为PRI, 非空约束</div><div class="line">+-------+-------------+------+-----+---------+-------+</div><div class="line">| Field | Type        | Null | Key | Default | Extra |</div><div class="line">+-------+-------------+------+-----+---------+-------+</div><div class="line">| id    | int(11)     | NO   | PRI | NULL    |       |</div><div class="line">| name  | varchar(20) | YES  |     | NULL    |       |</div><div class="line">+-------+-------------+------+-----+---------+-------+</div><div class="line">2 rows in set (0.00 sec)</div><div class="line"></div><div class="line">-- 查看创建表的定义</div><div class="line">SHOW CREATE TABLE user1\G</div><div class="line">*************************** 1. row ***************************</div><div class="line">       Table: user1</div><div class="line">Create Table: CREATE TABLE `user1` (</div><div class="line">  `id` int(11) NOT NULL,</div><div class="line">  `name` varchar(20) DEFAULT NULL,</div><div class="line">  PRIMARY KEY (`id`)</div><div class="line">) ENGINE=InnoDB DEFAULT CHARSET=utf8</div><div class="line">1 row in set (0.00 sec)</div><div class="line"></div><div class="line">INSERT INTO user1 VALUES(1,&apos;zrd&apos;);</div><div class="line">INSERT INTO user1 VALUES(12,&apos;King&apos;);</div><div class="line"></div><div class="line">SELECT * FROM user1;</div><div class="line">+----+------+</div><div class="line">| id | name |</div><div class="line">+----+------+</div><div class="line">|  1 | zrd  |</div><div class="line">| 12 | King |</div><div class="line">+----+------+</div><div class="line">2 rows in set (0.00 sec)</div><div class="line"></div><div class="line">-- 插入重复的字段进行测试</div><div class="line">INSERT INTO user1 VALUES(12,&apos;King&apos;);</div><div class="line">ERROR 1062 (23000): Duplicate entry &apos;12&apos; for key &apos;PRIMARY&apos;</div><div class="line"></div><div class="line"></div><div class="line">-- 多字段主键(复合主键)</div><div class="line">-- 由两字段来创建字段，一张表中只能有一个主键</div><div class="line">CREATE TABLE IF NOT EXISTS user2(</div><div class="line">id INT,</div><div class="line">username  VARCHAR(20),</div><div class="line">card CHAR(18),</div><div class="line">PRIMARY KEY(id,card)</div><div class="line">);</div><div class="line"></div><div class="line">DESC user2;</div><div class="line">+----------+-------------+------+-----+---------+-------+</div><div class="line">| Field    | Type        | Null | Key | Default | Extra |</div><div class="line">+----------+-------------+------+-----+---------+-------+</div><div class="line">| id       | int(11)     | NO   | PRI | 0       |       |</div><div class="line">| username | varchar(20) | YES  |     | NULL    |       |</div><div class="line">| card     | char(18)    | NO   | PRI |         |       |</div><div class="line">+----------+-------------+------+-----+---------+-------+</div><div class="line"></div><div class="line">INSERT INTO user2 VALUES(1,&apos;zrd&apos;,&apos;111&apos;);</div><div class="line"></div><div class="line">--由于 user2 使用id 和card来进行约束，所以满足其一即可</div><div class="line">INSERT INTO user2 VALUES(1,&apos;zrd rudong&apos;,&apos;112&apos;);</div><div class="line">SELECT * FROM user2;</div><div class="line">+----+------------+------+</div><div class="line">| id | username   | card |</div><div class="line">+----+------------+------+</div><div class="line">|  1 | zrd        | 111  |</div><div class="line">|  1 | zrd rudong | 112  |</div><div class="line">+----+------------+------+</div><div class="line">2 rows in set (0.00 sec)</div><div class="line"></div><div class="line">-- 插入两个复合主键都一样，则会报错</div><div class="line">INSERT INTO user2 VALUES(1,&apos;zrd rudong&apos;,&apos;112&apos;);</div><div class="line">ERROR 1062 (23000): Duplicate entry &apos;1-112&apos; for key &apos;PRIMARY&apos;</div><div class="line"></div><div class="line"></div><div class="line">-- 直接使用 KEY 也表示是使用主键(primary key)</div><div class="line">CREATE TABLE IF NOT EXISTS user3(</div><div class="line">id INT KEY,</div><div class="line">username  VARCHAR(20)</div><div class="line">);</div><div class="line"></div><div class="line">DESC user3;</div><div class="line">+----------+-------------+------+-----+---------+-------+</div><div class="line">| Field    | Type        | Null | Key | Default | Extra |</div><div class="line">+----------+-------------+------+-----+---------+-------+</div><div class="line">| id       | int(11)     | NO   | PRI | NULL    |       |</div><div class="line">| username | varchar(20) | YES  |     | NULL    |       |</div><div class="line">+----------+-------------+------+-----+---------+-------+</div><div class="line">2 rows in set (0.00 sec)</div></pre></td></tr></table></figure>
<h4 id="自增长-AUTO-INCREMENT"><a href="#自增长-AUTO-INCREMENT" class="headerlink" title="自增长 AUTO INCREMENT"></a>自增长 AUTO INCREMENT</h4><figure class="highlight plain"><table><tr><td class="code"><pre><div class="line">-- 自增长 AUTO INCREMENT</div><div class="line">默认从1开始，每次自增1</div><div class="line">要求：一张表中只能有一个字段为自增长字段；</div><div class="line">被标识成自增长的字段一定是主键</div><div class="line"></div><div class="line">CREATE TABLE IF NOT EXISTS user4(</div><div class="line">id SMALLINT KEY AUTO_INCREMENT,</div><div class="line">username VARCHAR(20)</div><div class="line">);</div><div class="line"></div><div class="line">-- 可以看到在 extra额外信息 发现auto_increment</div><div class="line">DESC user4;</div><div class="line">+----------+-------------+------+-----+---------+----------------+</div><div class="line">| Field    | Type        | Null | Key | Default | Extra          |</div><div class="line">+----------+-------------+------+-----+---------+----------------+</div><div class="line">| id       | smallint(6) | NO   | PRI | NULL    | auto_increment |</div><div class="line">| username | varchar(20) | YES  |     | NULL    |                |</div><div class="line">+----------+-------------+------+-----+---------+----------------+</div><div class="line">2 rows in set (0.00 sec)</div><div class="line"></div><div class="line">-- 插入数据几种方式</div><div class="line">-- 1 手动指定值</div><div class="line">INSERT INTO user4 VALUES(1,&apos;tomcat&apos;);</div><div class="line"></div><div class="line">-- 让auto_increment自动增长, 默认情况下在ID的最大值下加1</div><div class="line">INSERT INTO user4(username) VALUES(&apos;zrd&apos;); </div><div class="line">SELECT * FROM user4;</div><div class="line">+----+----------+</div><div class="line">| id | username |</div><div class="line">+----+----------+</div><div class="line">|  1 | tomcat   |</div><div class="line">|  2 | zrd      |</div><div class="line">+----+----------+</div><div class="line">2 rows in set (0.00 sec)</div><div class="line"></div><div class="line"></div><div class="line">INSERT INTO user4 VALUES(122,&apos;tomcat2&apos;);</div><div class="line">INSERT INTO user4(username) VALUES(&apos;zhourudong&apos;); </div><div class="line">SELECT * FROM user4;</div><div class="line">+-----+------------+</div><div class="line">| id  | username   |</div><div class="line">+-----+------------+</div><div class="line">|   1 | tomcat     |</div><div class="line">|   2 | zrd        |</div><div class="line">| 122 | tomcat2    |</div><div class="line">| 123 | zhourudong |</div><div class="line">+-----+------------+</div><div class="line">4 rows in set (0.00 sec)</div><div class="line"></div><div class="line">-- 可以看到下次自动增长值为124</div><div class="line">SHOW CREATE TABLE user4;</div><div class="line">+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+</div><div class="line">| Table | Create Table                                                                                                                                                                           |</div><div class="line">+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+</div><div class="line">| user4 | CREATE TABLE `user4` (</div><div class="line">  `id` smallint(6) NOT NULL AUTO_INCREMENT,</div><div class="line">  `username` varchar(20) DEFAULT NULL,</div><div class="line">  PRIMARY KEY (`id`)</div><div class="line">) ENGINE=InnoDB AUTO_INCREMENT=124 DEFAULT CHARSET=utf8 |</div><div class="line">+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+</div><div class="line">1 row in set (0.00 sec)</div><div class="line"></div><div class="line"></div><div class="line">-- 使用 NULL</div><div class="line">INSERT INTO user4 VALUES(NULL,&apos;nginx&apos;);</div><div class="line">SELECT * FROM user4;</div><div class="line">+-----+------------+</div><div class="line">| id  | username   |</div><div class="line">+-----+------------+</div><div class="line">|   1 | tomcat     |</div><div class="line">|   2 | zrd        |</div><div class="line">| 122 | tomcat2    |</div><div class="line">| 123 | zhourudong |</div><div class="line">| 136 | nginx      |</div><div class="line">+-----+------------+</div><div class="line"></div><div class="line">-- 使用DEFAULT</div><div class="line">INSERT INTO user4 VALUES(NULL,&apos;Apache&apos;);</div><div class="line">SELECT * FROM user4;</div><div class="line">+-----+------------+</div><div class="line">| id  | username   |</div><div class="line">+-----+------------+</div><div class="line">|   1 | tomcat     |</div><div class="line">|   2 | zrd        |</div><div class="line">| 122 | tomcat2    |</div><div class="line">| 123 | zhourudong |</div><div class="line">| 136 | nginx      |</div><div class="line">| 137 | Apache     |</div><div class="line">+-----+------------+</div><div class="line">6 rows in set (0.00 sec)</div><div class="line"></div><div class="line"></div><div class="line">-- 指定自增长从100 开始</div><div class="line">CREATE TABLE `user5` (</div><div class="line">`id` smallint(6) KEY  AUTO_INCREMENT,</div><div class="line">`username` varchar(20) DEFAULT NULL</div><div class="line">) AUTO_INCREMENT=100;</div><div class="line"></div><div class="line">SHOW CREATE TABLE user5\G</div><div class="line">*************************** 1. row ***************************</div><div class="line">       Table: user5</div><div class="line">Create Table: CREATE TABLE `user5` (</div><div class="line">  `id` smallint(6) NOT NULL AUTO_INCREMENT,</div><div class="line">  `username` varchar(20) DEFAULT NULL,</div><div class="line">  PRIMARY KEY (`id`)</div><div class="line">) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8</div><div class="line">1 row in set (0.00 sec)</div><div class="line"></div><div class="line">-- 修改自增长的值</div><div class="line">ALTER TABLE user5 AUTO_INCREMENT=500;</div><div class="line">SHOW CREATE TABLE user5\G</div><div class="line">*************************** 1. row ***************************</div><div class="line">       Table: user5</div><div class="line">Create Table: CREATE TABLE `user5` (</div><div class="line">  `id` smallint(6) NOT NULL AUTO_INCREMENT,</div><div class="line">  `username` varchar(20) DEFAULT NULL,</div><div class="line">  PRIMARY KEY (`id`)</div><div class="line">) ENGINE=InnoDB AUTO_INCREMENT=500 DEFAULT CHARSET=utf8</div></pre></td></tr></table></figure>
<h4 id="非空约束"><a href="#非空约束" class="headerlink" title="非空约束"></a>非空约束</h4><figure class="highlight plain"><table><tr><td class="code"><pre><div class="line"># 测试非空约束 NOT NULL </div><div class="line">CREATE TABLE IF NOT EXISTS user7 (</div><div class="line">id INT UNSIGNED KEY AUTO_INCREMENT,</div><div class="line">username VARCHAR(20) NOT NULL ,</div><div class="line">password CHAR(32) NOT NULL,</div><div class="line">age TINYINT UNSIGNED </div><div class="line">);</div><div class="line"></div><div class="line">DESC user7;</div><div class="line">+----------+---------------------+------+-----+---------+----------------+</div><div class="line">| Field    | Type                | Null | Key | Default | Extra          |</div><div class="line">+----------+---------------------+------+-----+---------+----------------+</div><div class="line">| id       | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |</div><div class="line">| username | varchar(20)         | NO   |     | NULL    |                |</div><div class="line">| password | char(32)            | NO   |     | NULL    |                |</div><div class="line">| age      | tinyint(3) unsigned | YES  |     | NULL    |                |</div><div class="line">+----------+---------------------+------+-----+---------+----------------+</div><div class="line">4 rows in set (0.00 sec)</div><div class="line"></div><div class="line"># 插入记录</div><div class="line"># id 自增 ， 只需要插入用户名和密码， age没有非空约束</div><div class="line">INSERT user7 (username,password) VALUES(&apos;ZRD&apos;,&apos;ZHOURUDONG&apos;);</div><div class="line"></div><div class="line">INSERT user7 (username,password,age) VALUES(&apos;tomcat&apos;,&apos;Apache&apos;,20);</div><div class="line">SELECT * FROM user7;</div><div class="line">+----+----------+------------+------+</div><div class="line">| id | username | password   | age  |</div><div class="line">+----+----------+------------+------+</div><div class="line">|  1 | ZRD      | ZHOURUDONG | NULL |</div><div class="line">|  2 | tomcat   | Apache     |   20 |</div><div class="line">+----+----------+------------+------+</div><div class="line">2 rows in set (0.00 sec)</div><div class="line"></div><div class="line"># 测试不满足情况</div><div class="line">INSERT user7 (username,password) VALUES(NULL,NULL);</div><div class="line">ERROR 1048 (23000): Column &apos;username&apos; cannot be null</div></pre></td></tr></table></figure>
<h4 id="默认值-DEFAULT"><a href="#默认值-DEFAULT" class="headerlink" title="默认值 DEFAULT"></a>默认值 DEFAULT</h4><figure class="highlight plain"><table><tr><td class="code"><pre><div class="line"># 默认值</div><div class="line"># 设置age默认值为18。 addr为beijing sex默认为男</div><div class="line"></div><div class="line">CREATE TABLE IF NOT EXISTS user8 (</div><div class="line">id INT UNSIGNED KEY AUTO_INCREMENT,</div><div class="line">username VARCHAR(20) NOT NULL ,</div><div class="line">password CHAR(32) NOT NULL,</div><div class="line">age TINYINT UNSIGNED DEFAULT 18,</div><div class="line">addr VARCHAR(50) NOT NULL DEFAULT &apos;beijing&apos;,</div><div class="line">sex ENUM (&apos;男&apos;,&apos;女&apos;,&apos;保密&apos;) NOT NULL DEFAULT &apos;男&apos;</div><div class="line">);</div><div class="line"></div><div class="line">DESC user8;</div><div class="line">+----------+----------------------------+------+-----+---------+----------------+</div><div class="line">| Field    | Type                       | Null | Key | Default | Extra          |</div><div class="line">+----------+----------------------------+------+-----+---------+----------------+</div><div class="line">| id       | int(10) unsigned           | NO   | PRI | NULL    | auto_increment |</div><div class="line">| username | varchar(20)                | NO   |     | NULL    |                |</div><div class="line">| password | char(32)                   | NO   |     | NULL    |                |</div><div class="line">| age      | tinyint(3) unsigned        | YES  |     | 18      |                |</div><div class="line">| addr     | varchar(50)                | NO   |     | beijing |                |</div><div class="line">| sex      | enum(&apos;男&apos;,&apos;女&apos;,&apos;保密&apos;)     | NO   |     | 男      |                |</div><div class="line">+----------+----------------------------+------+-----+---------+----------------+</div><div class="line">6 rows in set (0.01 sec)</div><div class="line"></div><div class="line"># 插入记录测试</div><div class="line">INSERT INTO user8(username,password) VALUES(&apos;ZRD&apos;,&apos;A123456&apos;);</div><div class="line"></div><div class="line">SELECT *  FROM user8;</div><div class="line">+----+----------+----------+------+---------+-----+</div><div class="line">| id | username | password | age  | addr    | sex |</div><div class="line">+----+----------+----------+------+---------+-----+</div><div class="line">|  1 | ZRD      | A123456  |   18 | beijing | 男  |</div><div class="line">+----+----------+----------+------+---------+-----+</div><div class="line">1 row in set (0.00 sec)</div><div class="line"></div><div class="line"># 插入完整的记录</div><div class="line">INSERT INTO user8 (username,password,age,addr,sex) VALUES(&apos;Alice&apos;,&apos;SDF123&apos;,23,&apos;上海&apos;,&apos;女&apos;);</div><div class="line">SELECT *  FROM user8;</div><div class="line">+----+----------+----------+------+---------+-----+</div><div class="line">| id | username | password | age  | addr    | sex |</div><div class="line">+----+----------+----------+------+---------+-----+</div><div class="line">|  1 | ZRD      | A123456  |   18 | beijing | 男  |</div><div class="line">|  2 | Alice    | SDF123   |   23 | 上海    | 女  |</div><div class="line">+----+----------+----------+------+---------+-----+</div><div class="line">2 rows in set (0.00 sec)</div><div class="line"></div><div class="line"># default 默认</div><div class="line">INSERT INTO user8 (id,username,password,age,addr,sex) VALUES(3,&apos;tomcat&apos;,&apos;SDF123&apos;,DEFAULT,DEFAULT,DEFAULT);</div><div class="line">SELECT * FROM user8;</div><div class="line">+----+----------+----------+------+---------+-----+</div><div class="line">| id | username | password | age  | addr    | sex |</div><div class="line">+----+----------+----------+------+---------+-----+</div><div class="line">|  1 | ZRD      | A123456  |   18 | beijing | 男  |</div><div class="line">|  2 | Alice    | SDF123   |   23 | 上海    | 女  |</div><div class="line">|  3 | tomcat   | SDF123   |   18 | beijing | 男  |</div><div class="line">+----+----------+----------+------+---------+-----+</div><div class="line">3 rows in set (0.00 sec)</div><div class="line"></div><div class="line"># 当用户没头像时给默认头像</div></pre></td></tr></table></figure>
<h4 id="唯一性约束"><a href="#唯一性约束" class="headerlink" title="唯一性约束"></a>唯一性约束</h4><figure class="highlight plain"><table><tr><td class="code"><pre><div class="line"># 唯一性约束 UNIQUE KEY</div><div class="line"># 一个主键，多个唯一性</div><div class="line"># 被标识成唯一约束后， 不能出现重复</div><div class="line"></div><div class="line">CREATE TABLE IF NOT EXISTS user9(</div><div class="line">id TINYINT UNSIGNED KEY AUTO_INCREMENT,</div><div class="line">username VARCHAR(20) NOT NULL UNIQUE,</div><div class="line">card CHAR(18) UNIQUE</div><div class="line">);</div><div class="line"></div><div class="line"># 可以看到key 中UNI约束</div><div class="line">DESC user9;</div><div class="line">+----------+---------------------+------+-----+---------+----------------+</div><div class="line">| Field    | Type                | Null | Key | Default | Extra          |</div><div class="line">+----------+---------------------+------+-----+---------+----------------+</div><div class="line">| id       | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |</div><div class="line">| username | varchar(20)         | NO   | UNI | NULL    |                |</div><div class="line">| card     | char(18)            | YES  | UNI | NULL    |                |</div><div class="line">+----------+---------------------+------+-----+---------+----------------+</div><div class="line">3 rows in set (0.00 sec)</div><div class="line"></div><div class="line">SHOW CREATE TABLE user9;</div><div class="line">+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+</div><div class="line">| Table | Create Table                                                                                                                                                                                                                                                                |</div><div class="line">+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+</div><div class="line">| user9 | CREATE TABLE `user9` (</div><div class="line">  `id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,</div><div class="line">  `username` varchar(20) NOT NULL,</div><div class="line">  `card` char(18) DEFAULT NULL,</div><div class="line">  PRIMARY KEY (`id`),</div><div class="line">  UNIQUE KEY `username` (`username`),</div><div class="line">  UNIQUE KEY `card` (`card`)</div><div class="line">) ENGINE=InnoDB DEFAULT CHARSET=utf8 |</div><div class="line">+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+</div><div class="line">1 row in set (0.00 sec)</div><div class="line"></div><div class="line"></div><div class="line">INSERT INTO user9 (username) VALUES(&apos;A&apos;);</div><div class="line">Query OK, 1 row affected (0.02 sec)</div><div class="line"></div><div class="line">INSERT INTO user9 (username) VALUES(&apos;A&apos;); # 受唯一性约束第二条记录插入失败</div><div class="line">ERROR 1062 (23000): Duplicate entry &apos;A&apos; for key &apos;username&apos;</div><div class="line"></div><div class="line"># NULL 为特殊字符，不算重复</div><div class="line">INSERT INTO user9(username,card) VALUES(&apos;B&apos;,NULL);</div><div class="line">INSERT INTO user9(username,card) VALUES(&apos;B1&apos;,NULL);</div><div class="line">INSERT INTO user9(username,card) VALUES(&apos;B2&apos;,NULL); </div><div class="line">SELECT * FROM user9;</div><div class="line">+----+----------+------+</div><div class="line">| id | username | card |</div><div class="line">+----+----------+------+</div><div class="line">|  1 | A        | NULL |</div><div class="line">|  3 | B        | NULL |</div><div class="line">|  4 | B1       | NULL |</div><div class="line">|  5 | B2       | NULL |</div><div class="line">+----+----------+------+</div></pre></td></tr></table></figure>
<h2 id="收回权限"><a href="#收回权限" class="headerlink" title="收回权限"></a>收回权限</h2><figure class="highlight plain"><table><tr><td class="code"><pre><div class="line">指定到数据库名</div><div class="line">REVOKE ALL PRIVILEGES  ON `bbs_db`.* FROM &apos;bbs_user&apos;@&apos;10.20.25.15&apos;;</div></pre></td></tr></table></figure>
<h2 id="查看权限"><a href="#查看权限" class="headerlink" title="查看权限"></a>查看权限</h2><figure class="highlight plain"><table><tr><td class="code"><pre><div class="line">SHOW GRANTS FOR &apos;bbs_user&apos;@&apos;10.20.25.15&apos;;</div></pre></td></tr></table></figure>
<h2 id="授权"><a href="#授权" class="headerlink" title="授权"></a>授权</h2><figure class="highlight plain"><table><tr><td class="code"><pre><div class="line">GRANT SELECT ON `bbs_db`.* TO &apos;bbs_user&apos;@&apos;10.20.25.15&apos; IDENTIFIED BY &apos;1YWRzZmFzZGYK@&apos;;</div></pre></td></tr></table></figure>
<h2 id="5-7"><a href="#5-7" class="headerlink" title="5.7"></a>5.7</h2><figure class="highlight plain"><table><tr><td class="code"><pre><div class="line">登录root账户后执行下面操作：</div><div class="line"></div><div class="line">1、创建用户,名字为userone,密码为123456</div><div class="line">　　create user userone identified by &apos;123456&apos;;</div><div class="line">2、创建名字为dbone的数据库</div><div class="line">　　create database dbone;</div><div class="line">3、授予dbone数据库的所有权限给userone用户：</div><div class="line">　　grant all on dbone.* to userone</div></pre></td></tr></table></figure>

        </div>

        <blockquote class="post-copyright">
    <div class="content">
        
<span class="post-time">
    最后更新时间：<time datetime="2017-09-08T07:02:37.066Z" itemprop="dateUpdated">2017-09-08 15:02:37</time>
</span><br>


        
        <a href="/2016/09/10/41-mysql基础使用/" target="_blank" rel="external">http://zhourudong.cn/2016/09/10/41-mysql基础使用/</a>
        
    </div>
    <footer>
        <a href="http://zhourudong.cn">
            <img src="/img/avatar.png" alt="cucy">
            cucy
        </a>
    </footer>
</blockquote>

        
<div class="page-reward">
    <a id="rewardBtn" href="javascript:;" class="page-reward-btn waves-effect waves-circle waves-light">赏</a>
</div>



        <div class="post-footer">
            
	<ul class="article-tag-list"><li class="article-tag-list-item"><a class="article-tag-list-link" href="/tags/mysql/">mysql</a></li></ul>


            
<div class="page-share-wrap">
    

<div class="page-share" id="pageShare">
    <ul class="reset share-icons">
      <li>
        <a class="weibo share-sns" target="_blank" href="http://service.weibo.com/share/share.php?url=http://zhourudong.cn/2016/09/10/41-mysql基础使用/&title=《mysql基础使用》 — Cucy的博客&pic=http://zhourudong.cn/img/avatar.png" data-title="微博">
          <i class="icon icon-weibo"></i>
        </a>
      </li>
      <li>
        <a class="weixin share-sns wxFab" href="javascript:;" data-title="微信">
          <i class="icon icon-weixin"></i>
        </a>
      </li>
      <li>
        <a class="qq share-sns" target="_blank" href="http://connect.qq.com/widget/shareqq/index.html?url=http://zhourudong.cn/2016/09/10/41-mysql基础使用/&title=《mysql基础使用》 — Cucy的博客&source=

1. 安装2. 基本使用" data-title=" QQ">
          <i class="icon icon-qq"></i>
        </a>
      </li>
      <li>
        <a class="facebook share-sns" target="_blank" href="https://www.facebook.com/sharer/sharer.php?u=http://zhourudong.cn/2016/09/10/41-mysql基础使用/" data-title=" Facebook">
          <i class="icon icon-facebook"></i>
        </a>
      </li>
      <li>
        <a class="twitter share-sns" target="_blank" href="https://twitter.com/intent/tweet?text=《mysql基础使用》 — Cucy的博客&url=http://zhourudong.cn/2016/09/10/41-mysql基础使用/&via=http://zhourudong.cn" data-title=" Twitter">
          <i class="icon icon-twitter"></i>
        </a>
      </li>
      <li>
        <a class="google share-sns" target="_blank" href="https://plus.google.com/share?url=http://zhourudong.cn/2016/09/10/41-mysql基础使用/" data-title=" Google+">
          <i class="icon icon-google-plus"></i>
        </a>
      </li>
    </ul>
 </div>



    <a href="javascript:;" id="shareFab" class="page-share-fab waves-effect waves-circle">
        <i class="icon icon-share-alt icon-lg"></i>
    </a>
</div>



        </div>
    </div>

    
<nav class="post-nav flex-row flex-justify-between">
  
    <div class="waves-block waves-effect prev">
      <a href="/2016/10/28/30-备份日志脚本/" id="post-prev" class="post-nav-link">
        <div class="tips"><i class="icon icon-angle-left icon-lg icon-pr"></i> Prev</div>
        <h4 class="title">备份日志脚本</h4>
      </a>
    </div>
  

  
    <div class="waves-block waves-effect next">
      <a href="/2016/09/10/18-python-解析式/" id="post-next" class="post-nav-link">
        <div class="tips">Next <i class="icon icon-angle-right icon-lg icon-pl"></i></div>
        <h4 class="title">python 解析式</h4>
      </a>
    </div>
  
</nav>



    














</article>

<div id="reward" class="page-modal reward-lay">
    <a class="close" href="javascript:;"><i class="icon icon-close"></i></a>
    <h3 class="reward-title">
        <i class="icon icon-quote-left"></i>
        谢谢大爷~
        <i class="icon icon-quote-right"></i>
    </h3>
    <div class="reward-content">
        
        <div class="reward-code">
            <img id="rewardCode" src="/img/wechat.jpg" alt="打赏二维码">
        </div>
        
        <label class="reward-toggle">
            <input id="rewardToggle" type="checkbox" class="reward-toggle-check"
                data-wechat="/img/wechat.jpg" data-alipay="/img/alipay.jpg">
            <div class="reward-toggle-ctrol">
                <span class="reward-toggle-item wechat">微信</span>
                <span class="reward-toggle-label"></span>
                <span class="reward-toggle-item alipay">支付宝</span>
            </div>
        </label>
        
    </div>
</div>



</div>

        <footer class="footer">
    <div class="top">
        
<p>
    <span id="busuanzi_container_site_uv" style='display:none'>
        站点总访客数：<span id="busuanzi_value_site_uv"></span>
    </span>
    <span id="busuanzi_container_site_pv" style='display:none'>
        站点总访问量：<span id="busuanzi_value_site_pv"></span>
    </span>
</p>


        <p>
            
                <span><a href="/atom.xml" target="_blank" class="rss" title="rss"><i class="icon icon-lg icon-rss"></i></a></span>
            
            <span>博客内容遵循 <a rel="license" href="https://creativecommons.org/licenses/by-nc-sa/4.0/deed.zh">知识共享 署名 - 非商业性 - 相同方式共享 4.0 国际协议</a></span>
        </p>
    </div>
    <div class="bottom">
        <p><span>cucy &copy; 2016 - 2017</span>
            <span>
                
                Power by <a href="http://hexo.io/" target="_blank">Hexo</a> Theme <a href="https://github.com/yscoder/hexo-theme-indigo" target="_blank">indigo</a>
            </span>
        </p>
    </div>
</footer>

    </main>
    <div class="mask" id="mask"></div>
<a href="javascript:;" id="gotop" class="waves-effect waves-circle waves-light"><span class="icon icon-lg icon-chevron-up"></span></a>



<div class="global-share" id="globalShare">
    <ul class="reset share-icons">
      <li>
        <a class="weibo share-sns" target="_blank" href="http://service.weibo.com/share/share.php?url=http://zhourudong.cn/2016/09/10/41-mysql基础使用/&title=《mysql基础使用》 — Cucy的博客&pic=http://zhourudong.cn/img/avatar.png" data-title="微博">
          <i class="icon icon-weibo"></i>
        </a>
      </li>
      <li>
        <a class="weixin share-sns wxFab" href="javascript:;" data-title="微信">
          <i class="icon icon-weixin"></i>
        </a>
      </li>
      <li>
        <a class="qq share-sns" target="_blank" href="http://connect.qq.com/widget/shareqq/index.html?url=http://zhourudong.cn/2016/09/10/41-mysql基础使用/&title=《mysql基础使用》 — Cucy的博客&source=

1. 安装2. 基本使用" data-title=" QQ">
          <i class="icon icon-qq"></i>
        </a>
      </li>
      <li>
        <a class="facebook share-sns" target="_blank" href="https://www.facebook.com/sharer/sharer.php?u=http://zhourudong.cn/2016/09/10/41-mysql基础使用/" data-title=" Facebook">
          <i class="icon icon-facebook"></i>
        </a>
      </li>
      <li>
        <a class="twitter share-sns" target="_blank" href="https://twitter.com/intent/tweet?text=《mysql基础使用》 — Cucy的博客&url=http://zhourudong.cn/2016/09/10/41-mysql基础使用/&via=http://zhourudong.cn" data-title=" Twitter">
          <i class="icon icon-twitter"></i>
        </a>
      </li>
      <li>
        <a class="google share-sns" target="_blank" href="https://plus.google.com/share?url=http://zhourudong.cn/2016/09/10/41-mysql基础使用/" data-title=" Google+">
          <i class="icon icon-google-plus"></i>
        </a>
      </li>
    </ul>
 </div>


<div class="page-modal wx-share" id="wxShare">
    <a class="close" href="javascript:;"><i class="icon icon-close"></i></a>
    <p>扫一扫，分享到微信</p>
    <img src="//api.qrserver.com/v1/create-qr-code/?data=http://zhourudong.cn/2016/09/10/41-mysql基础使用/" alt="微信分享二维码">
</div>




    <script src="//cdn.bootcss.com/node-waves/0.7.4/waves.min.js"></script>
<script>
var BLOG = { ROOT: '/', SHARE: true, REWARD: true };


</script>

<script src="/js/main.min.js?v=1.6.13"></script>


<div class="search-panel" id="search-panel">
    <ul class="search-result" id="search-result"></ul>
</div>
<template id="search-tpl">
<li class="item">
    <a href="{path}" class="waves-block waves-effect">
        <div class="title ellipsis" title="{title}">{title}</div>
        <div class="flex-row flex-middle">
            <div class="tags ellipsis">
                {tags}
            </div>
            <time class="flex-col time">{date}</time>
        </div>
    </a>
</li>
</template>

<script src="/js/search.min.js?v=1.6.13" async></script>






<script async src="//dn-lbstatics.qbox.me/busuanzi/2.3/busuanzi.pure.mini.js"></script>



<script>
(function() {
    var OriginTitile = document.title, titleTime;
    document.addEventListener('visibilitychange', function() {
        if (document.hidden) {
            document.title = '死鬼去哪里了！';
            clearTimeout(titleTime);
        } else {
            document.title = '(つェ⊂)咦!又好了!';
            titleTime = setTimeout(function() {
                document.title = OriginTitile;
            },2000);
        }
    });
})();
</script>



</body>
</html>
